Archive

Archive for August, 2012

September Chicago SQL Server User Group Meeting

August 22, 2012 1 comment

Chicago SQL Server User Group Logo
The September meeting of the Chicago SQL Server User Group will be Thursday, September 13th at the Microsoft Technology Center. If you are interested in attending, you can RSVP from the CSSUG Home Page. You can register for the group as well and receive information about upcoming meetings and group news.

A new section has been added to the home page for member blogs, and new posts to those blogs will be tweeted under the #chisql hash tag. If you are registered for the group and are interested in having your blog added, send your blog address here or post it in the comments here.

Finally, we are looking for presenters for future meetings. If you would like to present, post in the comments or send an e-mail to me here.

Categories: CSSUG, PASS, SQL Server Tags: ,

Database Corruption on the Principal AND the Mirror

August 15, 2012 Leave a comment

Over the past year, I’ve read a number of Paul Randal’s (b|t) blog posts regarding database corruption. I knew he had corrupt databases available for download at sqlskills.com, but I’d never found time to look at them.

That changed last week when the newest member of my team, Nick Schwab (b|t), was looking at mirroring. He asked about Automatic Page Repair, specifically what would happen if a page was corrupted on the principal and the mirror at the same time. I told him I did not know and asked him to run some tests.

Using these instructions from Kendra Little’s (b|t) blog, Nick was set to start corrupting pages.

Here is what he found. If the page was corrupted on the principal and was read from disk, the initial attempt would error but Automatic Page Repair would be triggered and subsequent access attempts would succeed. If a page was corrupted on the mirror, SQL Server would take no action until a role switch occurred and the page was read from disk on the new principal. At that point Automatic Page Repair would kick in.

That brings us to Nick’s question. If the same page was corrupted on both the principal and the mirror, Automatic Page Repair would pull the mirror copy of the page over and still fail on corruption. I understand that the possibility of this happening in a live environment is extremely small, but it is worth keeping in mind for mirrored applications. At the least, it is a good argument for having a restore strategy (see Myth Number 30-30 here).

Many thanks to Kendra and Paul for making this information available at their blogs.

The August Chicago SQL Server User Group Meeting

August 10, 2012 Leave a comment

Last night was the August meeting of the Chicago SQL Server User Group. It was the second meeting Aaron Lowe and I have run. Ira Whiteside of Melissa Data was kind enough to sponsor the meeting and was scheduled to present. Unfortunately, Ira ran into car problems on the way down and could not make it. In Ira’s absence, Aaron and I decided that we would each do a presentation. Aaron presented on Writing Professional Database Code, which was excellent. Statement terminators and version control for database code are two of Aaron’s suggestions I will bring back to my team.

I started off the meeting with a short presentation introducing Adam Machanic’s sp_whoisactive. If you do any troubleshooting of SQL Server issues, sp_whoisactive is a tool you should be using. It consolidates data from sp_who2, sys.dm_exec_requests and a number of other DMOs to give you a quick and complete picture of how each process is performing on your system. You can pull the execution plan for each process and have the option to generate deltas for a number of metrics over a period of time. The best part about the procedure is that it is free for download. The fact that Adam Machanic provides the code for free is a testament to the strength of the SQL Server community.

If you live in the Chicago area and are not registered for the user group, you can do so here. You will receive e-mail notification for upcoming meetings. We are looking for group members to present at upcoming meetings. If you are interested, you can e-mail me here. I hope to see you at an upcoming meeting.

Categories: CSSUG, DMO, sp_whoisactive Tags: ,

Calculating IO Deltas Using sys.dm_io_virtual_file_stats

August 2, 2012 Leave a comment

In a previous post, I showed how to calculate deltas for cumulative values from sys.dm_os_performance_counters. At the end of that post, I mentioned that the counter values are reset when the instance restarts, which can result in negative delta values. For the purposes of that script I did not care about negative values.

However, we are using cumulative values from sys.dm_io_virtual_file_stats to generate a report in Reporting Services, and the negative values are mucking up our graphs. The script I used to solve the problem is listed below. As usual, I hope you can put the ideas contained to use.

USE DUDDBA_MASTER
GO

-- Declare variables and create temp tables
DECLARE @sqlstr NVARCHAR(4000)
DECLARE @servername VARCHAR(40)
DECLARE @totalrows INT
DECLARE @currentrow INT
DECLARE @status INT
DECLARE @oldreadcnt BIGINT
DECLARE @newreadcnt BIGINT
DECLARE @newdate DATETIME
DECLARE @olddate DATETIME
DECLARE @mindate DATETIME
DECLARE @instancename VARCHAR(40)
DECLARE @tempstartup DATETIME
DECLARE @SQLString NVARCHAR(500);
DECLARE @ParmDefinition NVARCHAR(500);
DECLARE @startupdateout DATETIME


CREATE TABLE #serverlist 
(servername VARCHAR(40) NOT NULL)

CREATE TABLE #IODATA
(instancename VARCHAR(8)
,databasename VARCHAR(100)
,runtime DATETIME
,readcount BIGINT
,bytesread BIGINT
,writecount BIGINT
,byteswritten BIGINT)

CREATE TABLE #IODATANEW
(databasename VARCHAR(100)
,runtime DATETIME
,readcount BIGINT
,bytesread BIGINT
,writecount BIGINT
,byteswritten BIGINT)

CREATE TABLE #IODATAOLD
(databasename VARCHAR(100)
,runtime DATETIME
,readcount BIGINT
,bytesread BIGINT
,writecount BIGINT
,byteswritten BIGINT)

-- Initialize the @newdate and @olddate variables for the inner loop
SET @newdate = CONVERT(DATE,GETDATE())
SET @olddate = DATEADD(DD,-1,@newdate)

-- Build a list of linked servers for the outer loop, excluding non-2008 instances 
INSERT INTO #serverlist
SELECT 'DUL' + s1.DPC_SQL_CLUSTER_NM 
FROM DUT_POD_SQL_SERVER s1
INNER JOIN DUT_POD_SQL_VERSION s2
ON s1.DPC_SQL_CLUSTER_NM = s2.SVR_HOST_NM
WHERE s2.SVR_SQL_VERSION_TXT LIKE '%10.%' 

-- Store the @@ROWCOUNT value from the insert statement to control the outer loop
SET @totalrows = @@ROWCOUNT
SET @currentrow = 1

-- Execute the outer loop once for each linked server
WHILE @currentrow <= @totalrows
BEGIN

	-- Pull the first linked server name from the table
	SET @servername = (SELECT TOP 1 servername FROM #serverlist)
	
	-- Run the inner loop from the current date until 2012-01-01
	WHILE @olddate > '2012-01-01'
		BEGIN
			-- Build the dynamic sql string
			SET @sqlstr = 	
			'
			-- The DUT_FILE_IO table contains cumulative IO metrics from sys.dm_io_virtual_file_stats and is populated every 5 minutes
			-- The statement below pulls the values for the current day at midnight into #IODATANEW excluding non-user databases
			INSERT INTO #IODATANEW
			SELECT FIO_DATABASE_NM
			,FIO_RUN_TS
			,SUM(FIO_NBR_READS_CNT) AS [Total Reads]
			,SUM(FIO_BYTES_READ_CNT) AS [Total Bytes Read]
			,SUM(FIO_NBR_WRITES_CNT) AS [Total Writes]
			,SUM(FIO_BYTES_WRITE_CNT) AS [Total Bytes Written]
			FROM ' + @servername + '.duddba.dbo.DUT_FILE_IO 
			WHERE DATEPART(HH,FIO_RUN_TS) = 00
			AND DATEPART(MI,FIO_RUN_TS) = 00
			AND DATEPART(SS,FIO_RUN_TS) = 00
			AND CONVERT(DATE,FIO_RUN_TS) = ' + '''' + CONVERT(VARCHAR(30),@newdate,120)  + '''' +
			'
			AND FIO_DATABASE_NM NOT IN (''DUDDBA'',''DUDREPO'',''DUDDUMMY'',''DUDTRACE'',''master'',''model'',''msdb'')
			GROUP BY FIO_DATABASE_NM, FIO_RUN_TS
			ORDER BY FIO_RUN_TS DESC, FIO_DATABASE_NM

			-- The statement below pulls the values for the previous day at midnight into #IODATAOLD excluding non-user databases			
			INSERT INTO #IODATAOLD
			SELECT FIO_DATABASE_NM
			,FIO_RUN_TS
			,SUM(FIO_NBR_READS_CNT) AS [Total Reads]
			,SUM(FIO_BYTES_READ_CNT) AS [Total Bytes Read]
			,SUM(FIO_NBR_WRITES_CNT) AS [Total Writes]
			,SUM(FIO_BYTES_WRITE_CNT) AS [Total Bytes Written]
			FROM ' + @servername + '.duddba.dbo.DUT_FILE_IO 
			WHERE DATEPART(HH,FIO_RUN_TS) = 00
			AND DATEPART(MI,FIO_RUN_TS) = 00
			AND DATEPART(SS,FIO_RUN_TS) = 00
			AND CONVERT(DATE,FIO_RUN_TS) = ' + '''' + CONVERT(VARCHAR(30),@olddate,120)  + '''' +
			'
			AND FIO_DATABASE_NM NOT IN (''DUDDBA'',''DUDREPO'',''DUDDUMMY'',''DUDTRACE'',''master'',''model'',''msdb'')
			GROUP BY FIO_DATABASE_NM, FIO_RUN_TS
			ORDER BY FIO_RUN_TS DESC, FIO_DATABASE_NM
			
			
			-- The insert statement below calculates the delta values for the IO metrics collected
			-- The series of CASE statements accounts for the possibility of a negative value
			-- If the delta value is positive, it is used
			-- If it is negative, the raw value is used
			-- This is not the ideal solution, because it does not account for the cumulative values
			-- prior to the restart but it is good enough for the purposes of the reports we are running

			INSERT INTO #IODATA
			SELECT ' + '''' + SUBSTRING(@servername,4,8) + '''' +',n.databasename, n.runtime, 
			CASE
				WHEN ((n.readcount - o.readcount) > 0)
			THEN
				(n.readcount - o.readcount) 
			ELSE
				n.readcount
			END,
			CASE
				WHEN ((n.bytesread - o.bytesread)/1045876 > 0)
			THEN
				(n.bytesread - o.bytesread)
			ELSE
				n.bytesread
			END,
			CASE
				WHEN ((n.writecount - o.writecount) > 0)
			THEN
				(n.writecount - o.writecount)
			ELSE
				n.writecount
			END,
			CASE
				WHEN ((n.byteswritten - o.byteswritten)/1045876 > 0)
			THEN
				(n.byteswritten - o.byteswritten)/1045876 
			ELSE
			n.byteswritten
			END              
			FROM #IODATANEW n  
			INNER JOIN #IODATAOLD o  
			ON n.runtime = ' + '''' + CONVERT(VARCHAR(30),@newdate,120)  + '''' +
			'AND n.databasename = o.databasename
			WHERE o.runtime = ' + '''' + CONVERT(VARCHAR(30),@olddate,120)  + '''' +
			'
			'
			
			-- Execute the dynamic SQL string
			EXEC(@sqlstr)
			
			--Reset the new and old date variables
			SET @newdate = @olddate
			SET @olddate = DATEADD(DD,-1,@olddate)
			
			-- Clear out the new and old temp tables
			DELETE FROM #IODATANEW
			DELETE FROM #IODATAOLD
			
		END
	
	-- Insert the results for each linked server into the permanent table			
	INSERT INTO DUT_FILE_IO_DELTA
	SELECT * FROM #IODATA
	
	-- Delete the current linked server from the #serverlist table	
	DELETE FROM #serverlist WHERE servername = @servername

	-- Increment the loop counter
	SET @currentrow = @currentrow + 1
	
	-- Clear out the #IODATA table
	DELETE FROM #IODATA

	-- Decrement the new and old date variables	
	SET @newdate = CONVERT(DATE,GETDATE())
	SET @olddate = DATEADD(DD,-1,@newdate)
	
END


-- Clean up the temp tables at the end of the script
DROP TABLE #serverlist
DROP TABLE #IODATA
DROP TABLE #IODATAOLD
DROP TABLE #IODATANEW

Categories: DMO, SQL Server, T-SQL Tags: , ,