Month: September 2014

SQL Server Performance

It is all about the queries. You need only three bits of information about your queries: CPU, Duration & Reads.

SELECT TOP 50 qs.creation_time
, qs.execution_count
, qs.total_worker_time as cpu
, qs.total_elapsed_time as duration
, qs.total_logical_reads as reads
, t.[text]
FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
ORDER BY qs.total_worker_time DESC

CPU should roughly equal Duration, or C = D

CPU X 100 should roughly equal Reads, or C X 100 = R

If C > R, then we have Computation

If C = D and C X 100 = R, then we have a Runner

Waiter means we are waiting on something: I/O, blocking, latches, CPU.

Computation means we are doing something other than Reads: CPU bottleneck, spinlock, query compilation, UDF/function – computation, calculation, SQLCLR/XP, SQL Server code

Runner means nothing without a baseline. You would have to know that the query normally takes less time to run: Outdated stats, missing indexes, poorly designed query, suboptimal plan, parameter sniffing, optimizer timeout

Data to collect

Waiter: wait stats, query_plan, perfmon, profiler, blocker script/per_stats script, DMVs, Xevent

Computation: spinlock stats, query plan, profiler, set statistics time, statistics IO, query plan XML, Trace flags, Xperf, Kernrate, F1 Visual Studio, Query text, perfmon

Runner: query plan, schema, query text, statistics info, missing indexes info, index fragmentation

There are many DMVs, 3rd party software, and scripts that will help you gather this data.


DBCC SHRINKFILE not working…

Suppose you have a really large database and changes have been made by development so that the application is more efficient with the way it stores the data. Sufficient time has passed that all the old inefficiently stored data has been purged and you want to get some of that disk space back. You run DBCC Shrinkfile but your database isn’t shrinking. Well, if you have a lot of LOB data, it won’t so, how can you get that empty space out of your database?

1. Locate the tables that have lots of empty space
2. SELECT INTO newtable
3. DROP oldtable
4. Rename newtable to oldtable name

Now the DBCC Shrinkfile will release that free space back to the operating system

*Shrinking the database causes massive index fragmentation. The only way to remove index fragmentation without causing data file growth again is to use DBCC INDEXDEFRAG or ALTER INDEX … REORGANIZE.

Stored Procedure takes 15 minutes to run instead of its usual 1 minute

You also notice that it is doing a lot more reads than it should, and by a lot, I mean hundreds of millions versus tens of thousands. Now stop right there…

Without knowing anything else, what’s the first thing that comes to mind? Don’t worry. I’ll wait.

Purple Clock

Statistics! Yes, stale statistics. “But, Stacy,” you protest, “we have a job that runs every night that checks the fragmentation of the indexes and rebuilds the fragmented ones. This cannot be. You must be terribly mistaken.”

Sad girl

Check them anyway. In this case, it was, indeed, the statistics. Update stats with full scan resolved the issue right away. Remember, when things were running fine a couple of days ago, and now, for seemingly no reason, things are running slow, check your stats.

happy dance