Category: SQL Server

Sphinx For The Mahogany

All the helper objects for the below demos are available on my GitHub repo.

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

Demoman!

USE StackOverflow2010;
SET NOCOUNT ON;
GO 

/*
--Basic index info
SELECT *
FROM dbo.WhatsUpIndexes AS wui
WHERE wui.table_name = 'Users'
OPTION ( RECOMPILE );
GO   

--What's in the buffer pool?
SELECT *
FROM dbo.WhatsUpMemory AS wum
WHERE wum.object_name = 'Users'
OPTION ( RECOMPILE );
GO 
*/

--CREATE INDEX ix_whatever ON dbo.Users (Reputation);
GO 

SELECT *
FROM dbo.WhatsUpIndexes AS wui
WHERE wui.table_name = 'Users'
OPTION ( RECOMPILE );
GO            


DBCC DROPCLEANBUFFERS;
CHECKPOINT;
DBCC FREEPROCCACHE;
CHECKPOINT;
GO 5


SELECT *
FROM dbo.WhatsUpMemory AS wum
WHERE wum.object_name = 'Users'
OPTION ( RECOMPILE );
GO 

    SET STATISTICS TIME, IO, XML ON;
    SET NOCOUNT OFF;

    --/*Select a count of everything*/
    SELECT COUNT(*) AS records
    FROM dbo.Users AS u
    WHERE 1 = (SELECT 1);

    --/*Select a count of one user*/
 --   SELECT COUNT(*) AS records
 --   FROM dbo.Users AS u
 --   WHERE u.Id = 22656 
	--AND 1 = (SELECT 1);


    --/*Select a count of rep > 100k*/
    --SELECT COUNT(*) AS records
    --FROM dbo.Users AS u
    --WHERE u.Reputation >= 100000
    --AND 1 = (SELECT 1);

    SET NOCOUNT ON;
    SET STATISTICS TIME, IO, XML OFF;

SELECT *
FROM dbo.WhatsUpMemory AS wum
WHERE wum.object_name = 'Users'
OPTION ( RECOMPILE );
GO 

Generality vs. Specificity

If you’re stepping up to a SQL Server you’ve never seen before, you’re probably only armed with what people tell you the problem is.

Sometimes they’re right, more often they’re wrong. Even more often they’re just too vague, and it falls back to you to figure out exactly what’s “slow”.

As you start digging in, you’ll start noticing things you want to change, and building a mental case for why you want to change them:

• Maybe the CX waits will be really high, and no one has changed MAXDOP and CTFP from defaults
• Maybe there’s a lot of locking waits, and there’s a lot of overlapping indexes or you want to use RCSI
• Maybe there’s a lot of PAGEIOLATCH waits and there’s not many nonclustered indexes or you don’t have enough RAM

Those are good general patterns to watch out for, and while there may be regressions in some places, you’re likely to make the server a better place overall.

Sometimes you’ll get handed a query to tune.

Or more realistically, you get handed a few-thousand line stored procedure to tune. It probably calls other stored procedures, too.

Your job, one way or another, is to reduce the length of time between hitting F5 and having it complete.

For different sets of parameters.

Things That Change

In a perfect world, you’d change one variable (this could be a setting, and index, the way a query is written, or an actual variable being passed in), and see how the metrics you care about change.

But that hardly ever happens, does it?

You’ll probably:

• Change a few settings you’re confident about
• Deduplicate a bunch of indexes for a table at a time
• Adjust a bunch of things in a stored procedure as you scroll through
• Fix some Heaps all together

Look, it’s okay. You might need to be really effective and make a lot of progress quickly. Not everyone has weeks or months to make incremental changes towards better performance.

But you need to be aware of which metrics you’re hoping to improve when you make a change, and you need to be sure that the changes you make can actually make it wiggle.

For a query, it’s likely just a mix of elapsed time, CPU time, and memory grants. In a way, that’s a lot easier.

For a whole server, you need to understand the problems, and how SQL Server surfaces them.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.