Secrets of SQL Server Consultant
Well, I hope you are not looking for them from me. All of my secrets are common knowledge now. I was hoping you would share yours with us. Just leave them in the comments. I promise not to tell anyone. They can be our little secret, dawg. I’ll dap on it.
Am I losing my edge not specializing in SQL Server only and wearing a manager’s hat? I used to have a bag of tricks that I kept well guarded so I could get a free 10-20% performance boost right off the bat. After that, I walked on water and could do no wrong with the customer. The tricks are still valid but widely known. Even if not widely implemented, the cat is out of the bag.
So what were these tricks? First thing I would do is turn off the 3D screen saver. That is a free 50-60% reduction in CPU usage especially on those old dual and quad proc 550 MHz PII boxes. I like to joke but this is not a joke. I didn’t always run into this but it was relatively common considering most of these boxes didn’t even have a monitor.
The other two I learned in a post-conference workshop that Gert Drapers delivered. He dropped all kinds of internals knowledge plus obscure trace flags and DBCC’s. Sector alignment and waitstats were the secret weapons though. If you googled diskpar.exe or sector alignment back then, you would get nothing. The only thing on waitstats was Tom Davidson’s SQL mag article and the listings on http://sqldev.net. Both of these are common knowledge today and you are not on par unless they are in your tool box. Luckily, there is plenty on info on sector alignment and waitstats.
On top of that, even though these secrets are well documented, they are not often used. The same goes a lot of things in the SQL Server consultant tool box. Here are a few.
- DMV’s (Seriously!!)
- DBCC TRACEON(3604)
- XQUERY in Database Administration
- Trace and procedure cache aggregation reports
- Setting the RAID controller cache to 100% write 0% read
- Turning off TCP Chimney (Thanks Microsoft
) - Covering indexes
- Reading query plans
- Indexed views and persisted computed columns
- TCP Windows Tuning
- Proper memory configuration
- Automagically being able to tune without even seeing queries
Anyway, I don’t have any more consultant’s secrets and I have been giving away my tools here for the last couple of years. Throw me a bone. I am about as relevant as a 15 year old US Robotics 56k modem so leave the secret sauce below. KaiBuhBiThnx!1one.
Finding Index Scans due to Implicit Conversions
It is fairly widely known that implicit conversions of data types can cause scans. However, you may not be able to control what goes in your database, you may have inherited an app and we just are not perfect.
On top of that some can be tricky. For example, did you know that scope_identity and @@identity both return numeric data types. An AVG of a SMALLINT column returns an INT.
Here is a simple repro which the same query does an index scan instead of an index seek
CREATE TABLE t1
(
id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
, c1 VARCHAR(100)
)
GO
INSERT INTO t1(c1)
SELECT 1
GO 10000
INSERT INTO t1(c1)
SELECT 0
GO 10
CREATE INDEX ix ON t1 (c1)
CREATE PROC p1
@blah NVARCHAR(100)
AS
SELECT id FROM t1
WHERE c1 = @blah
EXEC p1 N'0'
CREATE PROC p2
@blah VARCHAR(100)
AS
SELECT id FROM t1
WHERE c1 = @blah
EXEC p2 '0'
DROP TABLE t1
DROP PROC p1
DROP PROC p2
I found a sample query from Umachandar Jayachandran and made some mod’s to it. It basically scans your procedure cache for index scans containing implicit conversions.
I have posted it at the SQLServerPedia Wiki here. It is the 2nd query listed.
Note: Implicit conversions may not always result in a scan.
Missing Indexes from a Different Angle
There are a lot of queries out there that can help you analyze the missing index DMV’s. I touch on them in this post. However, you can also get missing index info from XML showplan’s. The benefit of it doing that way is you see what query is missing the index. I have put together a query that returns the query text, the query plan, various stats and the cumulative impact of the missing index from the procedure cache. I could not figure out how to include the columns in the result set. There could be none or many for each column group. Pivot with xQuery? Maybe an xQuery guru will read this and help me out. However, just look in the query plan for the columns and create the index as needed.
Keep in mind that this query against a production system with a bloated 4GB cache probably isn’t a good idea during peak production hours. The query would not format well in this post so click here for it.
My RSS
-
My LifeStream
My Presence
