Bug Check Code Reference from MSDN
http://msdn.microsoft.com/en-us/library/windows/hardware/hh406232(v=vs.85).aspx
Great site with lots of collected information from an MVP
http://www.carrona.org/bsodindx.html
I once had a complete listing with search capability (http://www.techish.net/bugcodes/) but I no longer have this. I will soon recreate it.
This is a quick video of what I was working on for a little today. I created a batch script that will benchmark a disk(s) using SQLIO and record the output to a single file (or multiple, if necessary). After benchmarking completes, I run SQLIOResults and choose the output file that was created by my batch script.
Once SQLIOResults parses the SQLIO output and finishes inserting it into Excel, I create a PivotChart to compare the disk(s) IOPS and MB/s at each of the different test levels.
Hopefully someone else will find this useful. Also, if anyone knows how I can create a pivot chart in Powershell (if there’s a COM call), please let me know so I can completely automate this!
I have read numerous articles all over the ‘net regarding performance counters to monitor, etc.
This is a snapshot of a typical performance monitoring session I use to track down performance issues or review the system at any given point.
SQL and System Performance Counters
One of probably hundreds of SQL scripts out there. This is what I made and use… and I like it.
There are a lot of other scripts out there that are available and even an SQLIO GUI. Here are a few I looked at myself.
SQLIOToolSet
SQLIO GUI (screenshot)
SQLIO.Scripts
My script is a basic DOS batch file that automates collecting statistics on a few different drives when you’re testing. You need to make sure you have created the %TESTFILE% on the root of the drives you want to test. I create, and I suspect most others do as well, a testfile.dat on the root of each drive I test. You’ll need to create this before running the script or it will break!
If you want to extend this script, it’s easy. All the testing is put into a function called RUN_BENCHMARK which is called and passed a drive letter argument. So if you want to add more tests, feel free. Just mimic the existing code in that stanza and you should be ok.
SQLIO Batch Script
Finish SQLIO Benchmarking
SQLIOResults Combined Disk IOPS Graph
@echo off setlocal : This script will benchmark a HDD using SQLIO and store results in the : RESULTS_LOGFILE_NAME%_%X.txt where X is the drive letter(s) benchmarked : in the current path and TIME is the timestamp the script was run. : YOU MUST HAVE CREATED THE %TESTFILE% ON EACH DRIVE. : BUGFIX: Set time; strip colon and spaces. Was erroring on on spaces. set STRTIME=%TIME::=% set STRTIME=%STRTIME: =% : Set duration of each test in seconds set DURATION=30 : Set the buffering. N=none, Y=all, H=hdwr, S=sfwr set BUFFERING=H : Set latencies from (S=system, P=processor) timer set LATENCY=S : Filename of test file created already. You can specify a full path if needed. If spaces : exist in path or name, use quotes. set TESTFILE=testfile.dat : The log file name (prepend) - log file is stored in the same location this script is run : from. You could specifiy a path if you wanted. set RESULTS_LOGFILE_NAME=%COMPUTERNAME%_%STRTIME%_results : Drive(s) to run the tests on. Separate each drive with a comma. set TEST_DRIVES=E,F,G : Set the log to either 1-combine or 0-separate. If separated, a file will be generated : for each TEST_DRIVES disk. set COMBINE_LOG=1 : I should do some error checking, but I'm not going to. : Future plans: IF ["%TEST_DRIVES%"]==[] call:MissingDrive & goto EOF cls color 1F echo Benchmark of drive(s) %TEST_DRIVES% start: %DATE% %TIME% echo. echo. echo This will take some time. The background will become green once the echo benchmarking completes. Go grab some coffee and read up on the latest echo news of the world. echo. echo. for /D %%D in (%TEST_DRIVES%) do (call:RUN_BENCHMARK %%D) GOTO END :RUN_BENCHMARK IF [%1]==[] call:MissingDrive IF [%COMBINE_LOG%]==[1] ( set APPEND=COMBINED ) ELSE ( set APPEND=%1 ) echo Benchmark [%1] begin: %DATE% %TIME% echo [%1] RANDOM WRITES (64K) sqlio -d%1 -B%BUFFERING% -kW -frandom -t1 -o1 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kW -frandom -t2 -o1 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kW -frandom -t4 -o1 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kW -frandom -t8 -o1 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt echo [%1] RANDOM READS (64K) sqlio -d%1 -B%BUFFERING% -kR -frandom -t1 -o1 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -frandom -t2 -o1 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -frandom -t4 -o1 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -frandom -t8 -o1 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -frandom -t8 -o2 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -frandom -t8 -o4 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -frandom -t8 -o8 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -frandom -t8 -o16 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -frandom -t8 -o32 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -frandom -t8 -o64 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -frandom -t8 -o128 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt echo [%1] SEQUENTIAL READS (64K) sqlio -d%1 -B%BUFFERING% -kR -fsequential -t1 -o1 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -fsequential -t2 -o1 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -fsequential -t4 -o1 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -fsequential -t8 -o1 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -fsequential -t8 -o2 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -fsequential -t8 -o4 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -fsequential -t8 -o8 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -fsequential -t8 -o16 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -fsequential -t8 -o32 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -fsequential -t8 -o64 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kR -fsequential -t8 -o128 -s%DURATION% -b64 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt echo [%1] SEQUENTIAL WRITES (8K blocks to mimic SQL Logfiles) sqlio -d%1 -B%BUFFERING% -kW -fsequential -t1 -o1 -s%DURATION% -b8 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kW -fsequential -t2 -o1 -s%DURATION% -b8 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kW -fsequential -t4 -o1 -s%DURATION% -b8 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kW -fsequential -t8 -o1 -s%DURATION% -b8 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kW -fsequential -t8 -o2 -s%DURATION% -b8 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kW -fsequential -t8 -o4 -s%DURATION% -b8 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kW -fsequential -t8 -o8 -s%DURATION% -b8 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kW -fsequential -t8 -o16 -s%DURATION% -b8 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kW -fsequential -t8 -o32 -s%DURATION% -b8 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kW -fsequential -t8 -o64 -s%DURATION% -b8 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt sqlio -d%1 -B%BUFFERING% -kW -fsequential -t8 -o128 -s%DURATION% -b8 -L%LATENCY% %TESTFILE% >>%RESULTS_LOGFILE_NAME%_%APPEND%.txt echo Benchmark [%1] complete: %DATE% %TIME% echo. echo. GOTO EOF :MissingDrive color CF echo Missing drive. Specify %%TEST_DRIVE%% variable please. goto EOF :END color 2F echo Benchmark of drive(s) [%TEST_DRIVES%] completed: %DATE% %TIME% endlocal :EOF
This will show jobs that will run. I lost the link I found this at. Eventually, I need to add a few columns to report scheduling information, etc. This is a nice start though.
USE msdb
;WITH CTE AS (SELECT schedule_id, job_id, RIGHT('0'+CAST(next_run_time AS VARCHAR(6)),6) AS next_run_time, next_run_date
FROM sysjobschedules)
SELECT A.name Job_Name,
'Will be running today at '+
SUBSTRING(CONVERT(VARCHAR(10), CASE WHEN SUBSTRING (CONVERT(VARCHAR(10),next_run_time) , 1 ,2) > 12
THEN SUBSTRING (CONVERT(VARCHAR(10),next_run_time),1,2) -12
ELSE SUBSTRING (CONVERT(VARCHAR(10),next_run_time),1,2) END),1,2)
+':'+SUBSTRING (CONVERT(VARCHAR(10), next_run_time),3,2)
+':'+SUBSTRING (CONVERT(VARCHAR(10), next_run_time ),5,2) 'Scheduled At'
FROM sysjobs A ,CTE B
WHERE A.job_id = B.job_id
AND SUBSTRING(CONVERT(VARCHAR(10),next_run_date) , 5,2) +'/'+
SUBSTRING(CONVERT(VARCHAR(10),next_run_date) , 7,2) +'/'+
SUBSTRING(CONVERT(VARCHAR(10),next_run_date),1,4) = CONVERT(VARCHAR(10),GETDATE(),101)
AND (SUBSTRING( CONVERT(VARCHAR(10),
CASE WHEN SUBSTRING (CONVERT(VARCHAR(10),next_run_time) , 1 ,2) > 12
THEN SUBSTRING (CONVERT(VARCHAR(10),next_run_time) , 1 ,2) -12
ELSE SUBSTRING (CONVERT(VARCHAR(10),next_run_time) , 1 ,2) END),1,2)
+':'+SUBSTRING (CONVERT(VARCHAR(10), next_run_time ),3,2)
+':'+SUBSTRING (CONVERT(VARCHAR(10), next_run_time ),5,2)) >
SUBSTRING (CONVERT( VARCHAR(30) , GETDATE(),9),13,7)