Vacation…

Don't expect any activity here for at least a couple of weeks as we're leaving today for a long-overdue dive trip (on the Belize Aggressor) – our first diving for almost 18 months. I'll post lots of photos in December…

In the meantime, please go run the index survey code from my last post – the more data the better!

Cheers

SQLskills community mentoring

Jonathan Kehayias is such a nice guy. After our recent perf tuning Immersion Event in Chicago last month he had an idea for each of us to pick someone who's attended a SQLskills class and offer general mentoring (career, technical, professional development – whatever) to them for a few hours a month for six months. After six months we each pick someone else. His thinking was that this would be a cool way for SQLskills to give back to the community outside of the purely company-related things we do like blogging and twitter.

I think this is a really great idea. Back when I was at Microsoft I did a lot of mentoring when I managed teams there, plus in the company-wide mentoring program between senior and junior people in different groups. It's very rewarding to provide completely altruistic advice to someone and watch them grow.

Furthermore, we all have had people help us with our careers so it's important to pay that help forward.

So we're doing it, starting today with me, Joe, and Jonathan.

Joe is going to mentor Luke Jian, who's attended our IE1 and IE2 classes in Chicago this year:

Luke Jian is a well-versed IT professional with over 15 years of experience in the development and implementation of pharmaceutical and healthcare systems, demand driven supply chain management, ERP systems and technical infrastructure design.

Luke's current role is Sr. Solutions Architect with Physicians Interactive a leading resource for healthcare information, medication samples and medical decision support tools. Luke's experience include managing the IT Operations of over 50 clinics,  Oracle DBA for a supply chain software company, Teaching Assistant and Trainer.

Luke holds a Masters degree in software engineering from “Politehnica” University  of Timisoara, Romania with concentration on relational databases and  is fluent in English, Romanian, French and German. He became a US citizen in June 2011, the same week as his first public speaking engagement at SQL Saturday #82 in Indianapolis.

Luke writes at http://blog.sqlpositive.com  and he can be reached at sensware@gmail.com or on Twitter as @sensware.

Jonathan is going to mentor Steven Ormrod, who's attended our IE1, IE2, IE3, and IE4 classes in Dallas, Chicago, and Bellevue this year:

Steven Ormrod has been working as a Database Administrator for an international purveyor of natural and organic foods for the past several years.  Recently, Steven accepted a position with a global provider of orthotics and prosthetics.  His environment contains hundreds of servers spread across three different countries.  Clustering and consolidation projects have been his primary focus.

He has an MCITP for SQL Server 2008 in Database Administration and Development.

Prior to working as a DBA he has been a software developer, system administrator, and a teacher.  He also spent a summer bartending and hitchhiking across Europe.

When he is not tinkering with technology, he enjoys cooking, traveling, and snorkeling. He blogs at http://sqlavenger.wordpress.com/ and is on Twitter as @sqlavenger.

I'm going to mentor Brad Hoff, who's attended our IE1 and IE2 classes in Chicago this year:

Brad is originally from WA and is working in TX as a Lead SQL and Oracle DBA for an international power development company and energy marketer.

Brad has a degree is in Electronic Engineering and his background includes Development, Network/Systems/SAN admin, and WAN/T-Comm.

When Brad's not working with SQL server, he enjoys philosophy, debate, brain-teasers, learning, anything that challenges his mind. He has a beautiful wife and an awesome 17-month-old son. He blogs at http://www.sqlphilosopher.com/wp/ and is on Twitter as @sqlphilosopher.

I'd like to congratulate these three guys and look forward to us helping them out over the next six months!

Survey: wasted space from cluster keys (code to run)

Continuing with my "index health" series, I've got another piece of code for you to run.

This time I'm interested in the number of columns in your clustered indexes and the consequent amount of nonclustered index space used by the clustered index keys.

Again, you're going to be really interested to see the results on your servers. When I editorialize the results I'll provide another query for you to run which will make the data actionable on your server.

Here are some results from a random customer server (yes, we already knew about these – long story :-):

NCIndexes ClusterKeys KeyWidth TableRows            KeySpaceInBytes
——— ———– ——– ——————– ——————–
7         3           16       129902437            14549072944
1         3           12       29199817             350397804
10        2           12       1612919              193550280
5         2           5        4266671              106666775
2         2           8        5887697              94203152
5         4           20       827975               82797500
3         3           16       1215800              58358400
7         2           5        1497746              52421110
1         3           12       2667765              32013180
1         4           25       1033063              25826575
1         3           12       989320               11871840
2         2           8        278989               4463824
1         3           12       293736               3524832
4         2           5        160696               3213920

Feel free to send the results in any format you want – Excel spreadsheet works best though. Try not to add any columns to the result set – complicates the aggregation process.

The more results the better – thanks!

Here's the code:

IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE [name] = 'SQLskillsIKSpace')
    DROP TABLE tempdb.dbo.SQLskillsIKSpace;
GO
CREATE TABLE tempdb.dbo.SQLskillsIKSpace (
    DatabaseID SMALLINT,
    ObjectID INT,
    IndexCount SMALLINT,
    TableRows  BIGINT,
    KeyCount   SMALLINT,
    KeyWidth   SMALLINT);
GO

EXEC sp_MSforeachdb 
    N'IF EXISTS (SELECT 1 FROM (SELECT DISTINCT [name]
    FROM sys.databases WHERE [state_desc] = ''ONLINE''
        AND [database_id] > 4
        AND [name] != ''pubs''
        AND [name] != ''Northwind''
        AND [name] != ''distribution''
        AND [name] NOT LIKE ''ReportServer%''
        AND [name] NOT LIKE ''Adventure%'') AS names WHERE [name] = ''?'')
BEGIN
USE [?]

INSERT INTO tempdb.dbo.SQLskillsIKSpace
SELECT DB_ID (''?''), o.[object_id], 0, 0, 0, 0
FROM sys.objects o
WHERE o.[type_desc] IN (''USER_TABLE'', ''VIEW'')
    AND o.[is_ms_shipped] = 0
    AND EXISTS (
        SELECT *
        FROM sys.indexes
        WHERE [index_id] = 1
            AND [object_id] = o.[object_id]);

UPDATE tempdb.dbo.SQLskillsIKSpace
SET [TableRows] = (
    SELECT SUM ([rows])
    FROM sys.partitions p
    WHERE p.[object_id] = [ObjectID]
    AND p.[index_id] = 1)
WHERE [DatabaseID] = DB_ID (''?'');
 
UPDATE tempdb.dbo.SQLskillsIKSpace
SET [IndexCount] = (
    SELECT COUNT (*)
    FROM sys.indexes i
    WHERE i.[object_id] = [ObjectID]
    AND i.[is_hypothetical] = 0
    AND i.[is_disabled] = 0
    AND i.[index_id] != 1)
WHERE [DatabaseID] = DB_ID (''?'');

UPDATE tempdb.dbo.SQLskillsIKSpace
SET [KeyCount] = (
    SELECT COUNT (*)
    FROM sys.index_columns ic
    WHERE ic.[object_id] = [ObjectID]
    AND ic.[index_id] = 1)
WHERE [DatabaseID] = DB_ID (''?'');

UPDATE tempdb.dbo.SQLskillsIKSpace
SET [KeyWidth] = (
    SELECT SUM (c.[max_length])
    FROM sys.columns c
    JOIN sys.index_columns ic
    ON c.[object_id] = ic.[object_id]
    AND c.[object_id] = [ObjectID]
    AND ic.[column_id] = c.[column_id]
    AND ic.[index_id] = 1)
WHERE [DatabaseID] = DB_ID (''?'');

DELETE tempdb.dbo.SQLskillsIKSpace
WHERE
    ([KeyCount] = 1 AND [KeyWidth] < 9)
    OR [IndexCount] = 0 OR [TableRows] = 0;

END';
GO

SELECT
    [IndexCount] AS [NCIndexes],
    [KeyCount] AS [ClusterKeys],
    [KeyWidth],
    [TableRows],
    [IndexCount] * [TableRows] * [KeyWidth] AS [KeySpaceInBytes]
FROM tempdb.dbo.SQLskillsIKSpace
ORDER BY [KeySpaceInBytes] DESC;

DROP TABLE tempdb.dbo.SQLskillsIKSpace;
GO