Not too many people know about the intricacies of virtualization CPU scheduling and its impact on the performance of the VMs, so application owners out there – listen up! I’ve written about Ready Time (VMware ESXi) / Wait Time Per Dispatch (Microsoft Hyper-V) in the past, but a different challenge arises with VMs that have large vCPU count…
Tag: Performance
BCP and Code Page Fun
Quick, what’s the fastest and easiest way to get data out of a SQL Server table? Time’s up. It’s BCP. If you aren’t familiar with BCP it is a command line utility to bulk copy data out of, and in to tables. It has been around for donkeys years (technical term), and while it doesn’t…
Availability Groups & Reindexing
I’ve been working with AGs for the last year and have a couple of things as regards indexing that I thought would be good to share: Always sort in tempdb When you create, or rebuild an index you have the ability to have the index perform the sort of the data inside of tempdb. This…
FusionIO and the Go Faster Button
Nobody has ever said that FusionIO cards were slow (because they aren’t). Especially if you compare their performance to regular spinning media, or high performance SANs. After all, no SAN will allow you to measure storage write latency in microseconds. Anyone that has had their database reside on a FusionIO card has had nothing but…
Improving Performance When Querying Multiple DMVs
A couple of days ago I posted a stored procedure (sp_GetAGInformation) which queried multiple DMVs to pull together a bunch of Availability Group information. If you took a look at the code you would see that it used a couple of CTEs (Common Table Expressions). CTEs are a great way to do recursive work, and…
Performance Counters: Why It Depends
When you’re working through some vague performance issues (e.g., “SQL Server seems slow today”), one of the common things to do is to collect some performance counters. If you are collecting performance counters for the first time on the server, you don’t have anything to compare them against. This usually leads to searching the web…
SQL Server Virtual Machine vNUMA Sizing
A wonderful gentleman who attended my SQL PASS Summit session this year recently emailed me and asked me a great question about optimal setup for SQL Server virtual machine vCPU and vNUMA configurations. I thought about the question and realized that while the session went into a light level of detail, I can go much…
Grody to the VARCHAR(MAX)
This morning I was looking through a new vendor database to see exactly what I would be supporting and I stumbled upon a curious table. This table seemed quite normal to the naked eye until I glanced upon the data types. There were four, count them four fields set to VARCHAR(MAX) and two set to…
Lock Pages in Memory in SQL Server on VMware – Why or Why Not
Two weeks ago I presented my session entitled “Squeezing Top Performance from Your Virtualized SQL Server” at the SQL PASS Summit in Charlotte, North Carolina. One comment that I made during my presentation that seemed to spark some debates is how I normally enable the ‘Lock Pages in Memory’ feature of SQL Server as part…
Network Packet Size: to Fiddle With or Not to Fiddle With
A network pipeline isn’t nearly as pleasant to look at as the oil pipeline (or anything) in Alaska, but it’s something that DBAs should be aware of. There is a server configuration in SQL Server that controls the size of packet in which SQL Server sends out data. This setting is called network packet size…