An observant person has probably noticed that SQL queries requiring disk reads not only have longer duration but also higher CPU times. It is not hard then to deduce that disk access (for both HDD and SSD), which involves the OS performing an IO call, the SQL Server process finding a place in the buffer cache for the data pages, and possibly ...

Context switching can be hard to understand and measure.  As it turns out, you can use the PerfMon counter System >> Context switches/sec to count all of the switches occurring in and managed by SQLOS, regardless of the application(s) where it originated.  Note that this counter tracks all context switches UNLESS you’re using ...

Have you ever looked at the Windows Task Manager for a quick read on SQL Server 2005 memory consumption?   You can get good information from the Windows Task Manager about SQL Server memory, but you have to know what to look for. When running with AWE memory enabled on a 32-bit system, you may see an unusually low amount of ...

Let me start by saying that the contents of this post is not very advanced. If you have read the excellent paper ''Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005'', http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx and understood it, you would already know below, and much more... I was reading a ...

I'm in a situation where I have configured the new data collector functionality for three instances. And there's no way to undo the config performed by the wizard! It cannot be undone by the wizard, and BOL doesn't have information on how to do this. In fact, I suspect that you in the end need to use some of the undocumented data collector ...

[2008/08/25] This post has been modified significantly to correct some inaccurate statements because I mis-read Joe Chang's post. Joe Chang posted some interesting results using the TPC-H queries with the scale factor set to 10. I happened to have done something similar, and naturally noticed a rather significant difference between his results ...

I had been meaning to do a somewhat comprehensive review of SQL Server performance from versions 2000 to 2008 for both 32 and 64-bit on Data Warehouse type queries, with in depth examination of scaling in parallel execution plans.   For now, I can provide a short summary. The test platform is a Dell PowerEdge 2900 with 2 quad-core Xeon ...

Intel Dunnington  Yet another update with the publication TPC results for the Intel X7460 six core (Dunnington) X7460 2.67GHz 3x3M L2, 16M L3  Dell, HP lists availability Sep 15, 2008, IBM lists availability on the x3950M2 as Dec 10, Dell R900 with 4 x X7460, 2.67GHz, 6 core, 16M L3, $17,195 HP DL580G5 with 4 x ...

Is it quicker and/or lower overhead to insert into a heap vs. a clustered table?I don't know. So I decided to do a test. Some background information first: The test was inspired from a sidebar with Gert-Jan Strik in the open newsgroups. Basically I expressed that a heap doesn't automatically carry lower overhead... just because it is a heap. Now, ...

I was just about ready to unleash a long accumulating stream of rants against SAN vendors for pushing seriously obsolete computers as powerful storage systems. Of course, in a final check of products specs, I saw that EMC just announced the new Clariion CX4 line. The previous CX3 line was built around the Intel E7520 chipset, which was a ...