Trying to make time to get at root performance issues with SQL

Poor DB design coupled with poor queries, dynamic sql even can lead to crippling performance issue.  Things aren't "crippling", for me at the moment,  but theres still a lot that can be done. So where to start?

Well we need something to work with first. So go get some, PerfStatsScripts,  SQLDiag. Then feed it to SQL NEXUS. That should eventually lead to managing your indexes, whether missing, suggested or redundant. Another nugget, comes from K.Tripp Orz, sp_helpIndex. Visibility into what your tables have on them now. Hopefully your get into a grove where things will start to just fall into place. Said query is sucking life out of SQL. Its referencing, Table A and Table B. Review suggestions and view what there now. Make a  decision and review the results. Rinse wash repeat. I don't expect to end up with a clear glass, but hopefully this would have scrapped the scum off the surface and removed all the big particles and hair, eww.

Ready...Break!

Quick perf toolkit:
http://www.sqlskills.com/BLOGS/KIMBERLY/category/sp_helpindex-rewrites.aspx
~ Kimberly Tripp (so you know its legit)

SQL Nexus Tool + SQLDiag(already included) + SQL Server 2005 Performance Statistics Script


Commercial approach
~Throw me a bone so I may chase something worth chasing

-----

Goober: WHY IS IT DOING A TABLE SCAN? No matter what I do it just keeps doing a table scan? Update Stats? Restart SQL?

Bob: Eh, what index are you expecting to see used?

Goober: ...

Goober: .......

Goober: I hate you!!!

Goober: There was no index. I need to go talk to some people.

Bob: Super. Take care now, bye-bye then.

Mini Heart attack moment..Transaction log full then mirror busy

--// During index maintenance

The statement has been terminated.
Msg 9002, Level 17, State 5, Line 2
The transaction log for database 'VeryBIGDB' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

log_reuse_wait_desc: database_mirroring

Quick what to do what to do.....

ALTER DATBASE [VeryBIGDB] SET PARTNER OFF

BACKUP LOG [VeryBIGDB] TO DISK='Wherever.hbc'
Lucky for me I have a job already configured and part of log shipping strategy, so I just ran the job.

SHRINK LOG Files

Deep breathes WUSAaaaaaa.

But now....

:-(

Msg 1404, Level 16, State 5, Line 1
The command failed because the database mirror is busy. Reissue the command later.

C'mon, give me a frickenfarackenchikenmonkey break!!!!

Google said, sorry just restart the mirror from scratch.

MAN-UP.

Gotta go, running dark now.

UPDATE:1:26AM
Miraculously the mirrored DB stopped complaining about being busy.

Shot of adrenealine when i saw that.... DING DING DING... ROUND 3, FIGHT


Dug through the Log Shipping backups for said DB, with cmdline nugget:
C:\>dir /b L:\BOBSQLBACKUPS\TRN\VeryBIGDB_20110327_2*

C:\>dir /b L:\BOBSQLBACKUPS\TRN\VeryBIGDB_20110328_*

Add a dash of Excel, cut-n-paste, and a nifty formula:
="RESTORE LOG [VeryBIGDB] FROM DISK = 'L:\BOBSQLBACKUPS\TRN\" & A1 & "' WITH NORECOVERY,STATS=10;"
=
RESTORE LOG [VeryBIGDB] FROM DISK = 'L:\BOBSQLBACKUPS\TRN\VeryBIGDB_20110327_2000_LOG.hbc' WITH NORECOVERY,STATS=10;
RESTORE LOG [VeryBIGDB] FROM DISK = 'L:\BOBSQLBACKUPS\TRN\VeryBIGDB_20110327_2015_LOG.hbc' WITH NORECOVERY,STATS=10;
RESTORE LOG [VeryBIGDB] FROM DISK = 'L:\BOBSQLBACKUPS\TRN\VeryBIGDB_20110327_2030_LOG.hbc' WITH NORECOVERY,STATS=10;
...........
RESTORE LOG [VeryBIGDB] FROM DISK = 'L:\BOBSQLBACKUPS\TRN\VeryBIGDB_20110328_0115_LOG.hbc' WITH NORECOVERY,STATS=10;

......
Ran it with error cause not all were needed, too early to apply to the database....

BUT then:
10 percent processed.
20 percent processed.
30 percent processed.
......
....
;-(( Sniff sniff, tears of joy...

Resync mirror looking very promising... knock on wood...

WHAT IS THE WORD TO DESCRIBE this.....

I'm in a highly charged positive state of mind and feel like the energy and excitement is just swelling up inside. Like I want to go run as fast as I can till I drop. Waves of goosebumps keep crashing down on me. I wanna share the excitement yet I'm isolated in bottle.


BOTTLE IS FOREVER HALF FULL!!!

I can't hear your cries of "but but but", "that can't...", "you're such a kid", "you're so full of yourself"

I'm sorry you can't handle the awsomeness!!!!

160 Day straight Windows Server 2003x64, SQL 2005x64, 90+ DBS, 1.5TB

Sometimes you gotta smell the roses. Having to performs Maintenance to add in PCI-E SSD cards to SQL and just thought to check how long server been serving that masses.



And the uptime before that.....


I'm so happy, sniff, sniff

~Enjoy

Hyperbac to the rescue, again....syslnklgns

Another great feature of Hyperbac ver. 4.1.73.0.

I specify the version because they were aquired by Reg-gate who, seeminly severed this functionality and has relabeled, enhanced :-|, this former built in feature as SQL virtual restore.

EXEC master..sp_addlinkedserver
@server = 'masterbk',
@srvproduct = '',
@provider = 'HyperBac.oledbmtf',
@datasrc = 'C:\master_20110105_FULL.hbc,1'

SELECT * FROM masterbk..sys.syslnklgns
...

srvidlgnidnamestatusmodatepwdhash
10srvTreasureLnk02007-12-01 11:00:03.0000x01000000BOBSMAGICKEYOFHAPPYNESS11011001TESTACULARTOOLSTYHYPERBAC000

Now I can re-create the linked server object without needing to know the forgotten magic word.

~Enjoy

A few wasted hours, sob.....SQL 2k5 x86 sneaker

Fricken Fracken Chicken Chackers!!!


Admin(s) relayed to me server ready for my magic hands. Little did I know they had their hands all up in it. RDP'd in check the partition layout, corrected as needed. Gathered install files. Ready...break...literally. SQL x64 kept failing to install.

"Error 29549. Failed to install and configure assemblies E:\Program Files_64bit\Microsoft SQL Server\90\DTS\Tasks\Microsoft.SqlServer.MSMQTask.dll in the COM+ catalog. Error: -2146233087"

???

This a fresh install WTM??

What a pain, I finally discovered the Admin installed 2k5 32-bit, prior to handing off to me. Now why would I assume someone would have done that?? Grumble grumble...to make matters worse, I went to control panel --> Add remove, to try to start from scratch, by uninstalling all sql components, thinking the install somehow got corrupt. That somehow left me with an orphaned 32-bit install of SQL that I now can't remove with Add/Remove, BOOOOOOOOooooo. Now trying to clean up this mess.
DELETE COM+ Utilities (32 bit)
"The selected item cannot be deleted. This item is currently locked against deleting."

Craptacular!

:-(

Stable Virtual Environment? SvSAN HA + ESXI + OPENFILER + MISC HARDWARE

"Double, double toil and trouble; Fire burn, and caldron bubble." ~Shakespeare

For what ever reason that seems to capture my current state in time. Its been nothing short of another example of extreme IT engineering with this project. With adequate time and dedicated effort/focus this would seem like a cake walk. But with the sky falling, projects piling, fires burning, and only 24hours in a day, this is more of a cluster...k. But if Macgvyer can construct an airplane out of bamboo, what do I have to complain about. With so many ways to skin a cat, I offer up this small nugget of joy.....


This was done with everything that was already on-hand.

But just for laughs here is a list of items to build your our bamboo airplane, THAT REALLY FLYS, in theory ;-P

SoftwareSRCPriceQTYTotal
SVSAN with HA 2TBStormagic.com$1,9901$1,990
VMWARE Essentials plusvmware.com$4,2291$4,229
OpenFilerOpenFiler.com$01$0
Microsoft Windows Server 2008 R2 Standard - 5 CALs, 1 server (1-4 CPU) 2008Goolge Products$6601$660
Hardware
HP ProCurve J9450ANewEgg.com$3302$660
2U Supermicro X7DB3 Storage Server SAS 8GB 64bits *Refurb/UsedEbay.com$7004$2,800
Western Digital Caviar Blue WD10EALX-20PK 1TBNewEgg.com$1,2991$1,299
SuperMicro 1U server 2x Quad Core 3.16GHz 32gb 2TB *Refurb/UsedEbay.com$1,6002$3,200
BELKIN A3L791-14-BLU 14 ft. Cat 5E Blue Network Cable 20pkNewEgg.com$681$68
APC Smart-UPS 3000 Rack Mount XL 3U *Refurb/UsedRefurbUps.com$5502$1,100
GRAND$16,006
Disclaimer: These number, as of 2011-03-17 , are very very rough and just what I could find in less than 20 minutes. I'll also concede it may be missing additional components, but most can be correlated to the diagram above.

~Enjoy

SQL does UNC and local disk paths for FileName, FilePath, FolderPath

I frequently find my self needing to ensure my config tables are in sync with a Folder some network share or local drive. Whipped up a clean script to break it down simply


SET NOCOUNT ON
DECLARE
@CMD VARCHAR(500)
, @FolderPath VARCHAR(256)
, @FileExt VARCHAR(5) --// 'txt'
, @Subdirectories VARCHAR(3)

SELECT
--// UNC path may work too
@FolderPath = 'C:\Program Files\7-zip\' --// *include trailing back slash
, @FileExt = 'exe'
, @Subdirectories = 'YES' --// 'YES' | '' / 'NO'

SELECT
@CMD = 'dir /B /Aa'
+ CASE WHEN ISNULL(@Subdirectories, '') = 'YES' THEN '/S'
ELSE ''
END + ' ' + '"' + @FolderPath + CASE WHEN LEN(@FileExt) >= 1
THEN '*.' + @FileExt
ELSE ''
END + '"'
PRINT @CMD

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
DROP TABLE #tmp

CREATE TABLE #tmp (filePath VARCHAR(2000))

INSERT INTO #tmp
EXECUTE master.dbo.xp_cmdshell @CMD ;
--//Used CTE to provide some flexibility. Typically when looking for files
--// I need to compare configuration info like folder path or files in a
--// Config table. This allows for clean Joins.
WITH CTE
AS (
SELECT
CASE WHEN CHARINDEX('\', REVERSE(filePath)) = 0
THEN @FolderPath + filePath
ELSE filePath
END [FilePath]
, CASE WHEN CHARINDEX('\', REVERSE(filePath)) = 0
THEN @FolderPath
ELSE LEFT(filePath,
LEN(filepath) - CHARINDEX('\',
REVERSE(filePath))
+ 1)
END [FolderPath]
, CASE WHEN CHARINDEX('\', REVERSE(filePath)) = 0
THEN filePath
ELSE RIGHT(filePath,
CHARINDEX('\', REVERSE(filePath)) - 1)
END [FileName]
FROM
#tmp
WHERE
LEN(ISNULL(filePath, '')) > 0
)
SELECT
*
FROM
CTE




~Enjoy

a little nugget to clean up a raw data import.

I may be reinventing the wheel here, but it was late and was having a hard time googling for some reason. Needed something that I could hand off to someone to go crazy with in-loading data from raw text files, so I whipped this up.



/* 
Typically you would right click a db > Task > Import Data.
After data is imported you can use this script to create a cleaner base table.
Biggest headache is typically trying to identify the proper data type and lengths
This script attempts to handle the most common denominator, INT, DateTime, VarChar, and Decimal.

@LastColumn quirk, if importing CSV and last column contains "," the data is bad, so make best
effort to load and then go to source and work it out.

End result is Print statement that will create table x(base table) and
an insert into statement

*/
SET NOCOUNT ON
DECLARE
@ROWSAFFECTED INT
, @Count INT
, @Counter INT
, @ColumName VARCHAR(128)
, @LastColumn VARCHAR(128)
, @TableName VARCHAR(128)
, @dSQL VARCHAR(4000)
, @SaidTABLE VARCHAR(128)

SELECT
@SaidTABLE = 'CampaignResults_raw'
,@LastColumn = 'DateCreated'

IF OBJECT_ID('tempdb..#tCol') IS NOT NULL
DROP TABLE #tCol

SELECT
ORDINAL_POSITION IDX
, TABLE_NAME
, COLUMN_NAME
, CASE WHEN RIGHT(COLUMN_NAME, 2) = 'id' THEN 'INT'
WHEN COLUMN_NAME LIKE '%date%'
OR COLUMN_NAME LIKE '%time%' THEN 'DateTime'
ELSE 'VARCHAR'
END DT
, 0 MaxLen
INTO
#tCOL
FROM
INFORMATION_SCHEMA.Columns
WHERE
TABLE_NAME = @SaidTABLE
ORDER BY ORDINAL_POSITION

SELECT
@ROWSAFFECTED = @@ROWCOUNT
, @Counter = 1

IF @ROWSAFFECTED = 0
RETURN

SELECT
@Count = @ROWSAFFECTED
WHILE @Counter <= @Count
BEGIN
SELECT
@ColumName = COLUMN_NAME
, @TableName = TABLE_NAME
FROM
#tCOL
WHERE
IDX = @Counter

SELECT
@dSQL = 'UPDATE t
SET DT = CASE WHEN xINT = 0 THEN ''INT''
WHEN xDecimal =0 THEN ''Decimal''
WHEN xDate <0 THEN ''DATE''
ELSE ''VARCHAR'' END
,MaxLen = c.MaxLen
FROM
#tCOL t
JOIN
(
SELECT
' + LTRIM(STR(@counter)) + ' IDX
,SUM(CASE WHEN ISNUMERIC(LTRIM(RTRIM( [' + @ColumName
+ '] + ''e0'' ))) = 1 AND [' + @ColumName + '] NOT LIKE ''%.%'' THEN 0 ELSE 1 END) xINT
,SUM(CASE WHEN ISNUMERIC(LTRIM(RTRIM( [' + @ColumName
+ '] + ''e0'' ))) = 1 AND [' + @ColumName + '] LIKE ''%.%'' THEN 0 ELSE 1 END) xDecimal
,SUM(CASE WHEN ISDATE(LTRIM(RTRIM([' + @ColumName
+ ']))) = 1 THEN -1 ELSE 1 END) xDate
,MAX(LEN(RTRIM(LTRIM([' + @ColumName + '])))) MaxLen
FROM
' + @TableName + '
WHERE
[' + @LastColumn + '] NOT LIKE ''%,%''
AND [' + @ColumName
+ '] IS NOT NULL) c ON c.IDX = t.IDX AND t.IDX = '
+ LTRIM(STR(@counter))
--PRINT @dSQL
EXEC (@dSQL)
SELECT
@Counter = @Counter + 1
END

DECLARE @tSTR VARCHAR(8000)

SELECT
@tSTR = REPLACE('CREATE TABLE x('
+ (
SELECT
'
' + CASE WHEN IDX = 1 THEN ''
ELSE ','
END + '[' + COLUMN_NAME + ']' + CASE WHEN DT = 'INT' THEN ' [INT]'
WHEN DT = 'Decimal' THEN ' [Decimal]'
WHEN DT = 'DATE' THEN ' [DateTime]'
ELSE ' VarChar(' + LTRIM(STR(MaxLen))
+ ')'
END
FROM
#tCOL
ORDER BY IDX
FOR
XML PATH('')
), '&#x0D;', '') + ')'
PRINT @tSTR

--DECLARE @tSTR VARCHAR(8000)

SELECT
@tSTR = REPLACE('INSERT INTO X SELECT '
+ (
SELECT
'
' + CASE WHEN IDX = 1 THEN ''
ELSE ','
END + 'NULLIF(' + CASE WHEN DT IN ('INT','Decimal','Date') THEN '[' + COLUMN_NAME + ']'
ELSE 'LTRIM(RTRIM([' + COLUMN_NAME + ']))'
END + ' ,''NULL'')'
FROM
#tCOL
--where DT = 'INT'
ORDER BY IDX
FOR
XML PATH('')
), '&#x0D;', '') + ' FROM ' + @SaidTable + '
WHERE [' + @LastColumn + '] NOT LIKE ''%,%'''

PRINT @tSTR

~Enjoy

DRBD Openfiler FINALLY


Wee hours never good when have to follow instructions to the T. I keep fighting with this, if not the partitions, then zero out thing. If not zero out, then its oversight on glaring message that just seemed to blend into screen. If not that then the drbd.conf was off. If not that then....one or the other would constantly be "Unconfigured"

m:rescsstdspmountedfstype
0:cluster_metadataConnectedSecondary/SecondaryInconsistent/InconsistentC
1:vg0drbdConnectedSecondary/SecondaryInconsistent/InconsistentC

m:rescsstdspmountedfstype
0:cluster_metadataConnectedPrimary/SecondaryUpToDate/InconsistentC
...sync'ed:0.30%(475751/476820)M
1:vg0drbdSyncSourcePrimary/SecondaryUpToDate/InconsistentC

Well on to HA. haresource what?

Man this was a chore... Many THANKS to google and freinds...this one did it for me.