Although this is great for making sense of code that is completely mashed, maybe retrieved from a raw text with zero formatting or something, It should not replace developing a proper coding style.
Friday, January 21, 2011
SQL Formatter Link
I found this link which has a formatter that will take any valid SQL code and format it based on the parameters you pass into it:
Performance Data In SQL Table...
Dude this is cool. I learned how to dump performance counter data into SQL tables using 'perftype' in command shell. This could be helpful in many ways. First its more secure then dumping data in excel or CSV file, and you can query the data. This gives us opportunity to find patterns in the performance data easily. Its a few easy steps to accomplish this...
1. Create a database to hold the data
2. Create a DSN to connect to the database
3. Create a txt file with a list of performance counters
4. Pass in cmd in cmd shell to run counters from text file and dump results in the database you created.
1. Create a database to hold the data
It does not matter what the database name is but I created a database named: PerfmonCollector. Dont worry about creating tables because the system will create them automatically when you execute the perftype command. Here is some tsql to create the database...
CREATE DATABASE [PerfmonCollector] ON PRIMARY
( NAME = N'PerfmonCollector', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PerfmonCollector.mdf' , SIZE = 51200KB , FILEGROWTH = 10240KB )
LOG ON
( NAME = N'PerfmonCollector_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PerfmonCollector_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
2. Create a DSN to connect to the database
Second we would need to connect Typeperf to SQL Server. Let’s run the ODBC Data Source Administrator (we can access it by clicking Run… and then ‘odbcad32.exe’).
In the tabs of the administrator we see ‘User DSN’ and ‘System DSN’. The difference is that the User DSN is visible only to the current user, and the System DSN is visible to all users of the machine, including the NT services. So, let’s choose a ‘User DSN’, since we do not want anyone but us to access our database. Let’s create it:
1. Click Add… button and select SQL Server driver type
2. Click ‘Finish’. A Data Source wizard screen will show up:
3. Fill in the name of the Data Source and the SQL Server instance name.
4. In the next screen we would have to provide the login details:
I will use Windows authentication. Click next.
5. In this screen it is important to select our ‘PerfmonCollector’ database:
Click Next.
6. In this screen we would leave the settings as default:
Click Finish.
7. In this screen you will be presented with an overview of the settings and with a chance to test our connection.
Click the ‘Test Data Source…’ and make sure that the test is successful.
STEPS 3 & 4...
Now that we have a database and a connection to the database, all we need to do now is create a text file with the list of counters and pass in a command to run the counters on the server in question. For this I developed a script that would write the selected counters to a text file as well as run the proper perftype command into the command shell. The command dumps all the performance data it retrieved into three tables which get created automatically in the database you created. These tables are…
Dbo.counterdata
Dbo.counterdetails
Dbo.displaytoid
The script has some variables that need to be set before you run. Before I give you the code let me go over these variables:
@SetName <-- This will be the name of you're dataset and will appear in one of the tables
@server <-- Set this to the server you want to run the counters on
@path <-- This should be set to the full path of the text file which contains the variables. You do not have to create the file first just set the full path with file name and the script will create the file and add the counters you selected.
@DSN <-- This will be the name of the DSN you created in Step 2
@SI <-- This value is the sampling interval in seconds. So it determines how long the counters will run.
@SC <-- This value is the number of samples to collect. So for instance if you set @SI to 15 and @SC to 4, then the counters will run 4 times at 15 second intervals for a total of one minute.
@Truncate <-- Setting this value to 1 will truncate the tables in the database you created first. This will ensure that the new performance data is the only data in those tables.
Many basic performance counters are listed in the script. If you run the script as is, all counters will be added to the text file which will cause all counters to be run against the specified server. To limit this just comment out the counters you don't need.
Here is the SQL...
SET NOCOUNT ON
DECLARE
@server VARCHAR(50)
, @path VARCHAR(100)
, @cmd VARCHAR(500)
, @cmdshell VARCHAR(200)
, @DSN VARCHAR(50)
, @SI VARCHAR(5)
, @SC VARCHAR(5)
, @Truncate BIT
, @SetName VARCHAR(50)
--------------------------------------------------------------------------------------------------------------------------
SET @SetName = 'PerfDataTest' --Name the Set of Data
SET @server = 'servername' --Set Server Name in which you want to document counters
SET @path = '"filepath.txt"' --Set path in which counters text file will be
SET @DSN = 'DSN_Name' --Enter DSN Name. This will allow the script to generate the needed cmd text to pass into cmdshell
SET @SI = '15' --Set the sampling interval in seconds
SET @SC = '4' --Set the number of samples to collect
SET @Truncate = 1 --Set to 1 to truncate PerfmonCollector Tables
--------------------------------------------------------------------------------------------------------------------------
IF @Truncate = 1
BEGIN
TRUNCATE TABLE PerfmonCollector.dbo.counterData
TRUNCATE TABLE PerfmonCollector.dbo.counterDetails
TRUNCATE TABLE PerfmonCollector.dbo.DisplayToID
END
CREATE TABLE #Output (txt varchar(1000))
--Deletes Existing File
SET @cmd = 'del ' + @path
INSERT INTO #Output EXEC master..xp_cmdshell @cmd
--System Counters
SET @cmd = 'echo \Memory\Pages/sec >>' + @path INSERT INTO #Output EXEC master..xp_cmdshell @cmd
SET @cmd = 'echo \Memory\Available Bytes >>' + @path INSERT INTO #Output EXEC master..xp_cmdshell @cmd
SET @cmd = 'echo \Processor(_Total)\% Processor Time >>' + @path INSERT INTO #Output EXEC master..xp_cmdshell @cmd
SET @cmd = 'echo \Process(LSASS)\% Processor Time >>' + @path INSERT INTO #Output EXEC master..xp_cmdshell @cmd
SET @cmd = 'echo \Process(w3wp)\% Processor Time >>' + @path INSERT INTO #Output EXEC master..xp_cmdshell @cmd
SET @cmd = 'echo \Process(w3wp)\Working Set >>' + @path INSERT INTO #Output EXEC master..xp_cmdshell @cmd
SET @cmd = 'echo \Process(w3wp)\Private Bytes >>' + @path INSERT INTO #Output EXEC master..xp_cmdshell @cmd
SET @cmd = 'echo \Process(w3wp)\Page Faults/sec >>' + @path INSERT INTO #Output EXEC master..xp_cmdshell @cmd
SET @cmd = 'echo \Process(w3wp)\Working Set >>' + @path INSERT INTO #Output EXEC master..xp_cmdshell @cmd
SET @cmd = 'echo \Process(w3wp)\Working Set Peak >>' + @path INSERT INTO #Output EXEC master..xp_cmdshell @cmd
SET @cmd = 'echo \Process(w3wp)\Virtual Bytes >>' + @path INSERT INTO #Output EXEC master..xp_cmdshell @cmd
SET @cmd = 'echo \Process(w3wp)\Virtual Bytes Peak >>' + @path INSERT INTO #Output EXEC master..xp_cmdshell @cmd
SET @cmd = 'echo \Process(w3wp)\Private Bytes >>' + @path INSERT INTO #Output EXEC master..xp_cmdshell @cmd
SET @cmd = 'echo \Process(w3wp)\Page File Bytes >>' + @path INSERT INTO #Output EXEC master..xp_cmdshell @cmd
SET @cmd = 'echo \Process(w3wp)\Page File Bytes Peak >>' + @path INSERT INTO #Output EXEC master..xp_cmdshell @cmd
SET @cmd = 'echo \Process(OWSTIMER)\% Processor Time >>' + @path INSERT INTO #Output EXEC master..xp_cmdshell @cmd
SET @cmd = 'echo \ASP.NET\Request Execution Time >>' + @path INSERT INTO #Output EXEC master..xp_cmdshell @cmd
SET @cmd = 'echo \ASP.NET\Request Wait Time >>' + @path INSERT INTO #Output EXEC master..xp_cmdshell @cmd
SET @cmd = 'echo \ASP.NET\Requests Queued >>' + @path INSERT INTO #Output EXEC master..xp_cmdshell @cmd
SET @cmd = 'echo \ASP.NET\Requests Rejected >>' + @path INSERT INTO #Output EXEC master..xp_cmdshell @cmd
SET @cmd = 'echo \ASP.NET\Worker Process Restarts >>' + @path INSERT INTO #Output EXEC master..xp_cmdshell @cmd
SET @cmd = 'echo \ASP.NET\Application Restarts >>' + @path INSERT INTO #Output EXEC master..xp_cmdshell @cmd
SET @cmd = 'echo \System\Context Switches/sec >>' + @path INSERT INTO #Output EXEC master..xp_cmdshell @cmd
--Back End (SQL Server) Performance Counters
SET @cmd = 'echo \Processor(_Total)\% Processor Time >>' + @path INSERT INTO #Output EXEC master..xp_cmdshell @cmd
SET @cmd = 'echo \Memory\Pages/sec >>' + @path INSERT INTO #Output EXEC master..xp_cmdshell @cmd
SET @cmd = 'echo \System\Context Switches/sec >>' + @path INSERT INTO #Output EXEC master..xp_cmdshell @cmd
SET @cmd = 'echo \Process(sqlservr)\% Processor Time >>' + @path INSERT INTO #Output EXEC master..xp_cmdshell @cmd
SET @cmd = 'echo \Process(sqlservr)\Working Set >>' + @path INSERT INTO #Output EXEC master..xp_cmdshell @cmd
SET @cmd = 'echo \Process(sqlservr)\Private Bytes >>' + @path INSERT INTO #Output EXEC master..xp_cmdshell @cmd
SET @cmd = 'echo \SQLServer:General Statistics\User Connections >>' + @path INSERT INTO #Output EXEC master..xp_cmdshell @cmd
SET @cmd = 'echo \SQLServer:Databases\Transactions/sec >>' + @path INSERT INTO #Output EXEC master..xp_cmdshell @cmd
SET @cmd = 'echo \SQLServer:Locks(_Total)\Number of Deadlocks/sec >>' + @path INSERT INTO #Output EXEC master..xp_cmdshell @cmd
SET @cmd = 'echo \SQLServer:Locks(_Total)\Lock Waits/sec >>' + @path INSERT INTO #Output EXEC master..xp_cmdshell @cmd
SET @cmd = 'echo \SQLServer:Locks(_Total)\Lock Wait Time (ms) >>' + @path INSERT INTO #Output EXEC master..xp_cmdshell @cmd
SET @cmd = 'echo \SQLServer:SQL Statistics\Batch Requests/sec >>' + @path INSERT INTO #Output EXEC master..xp_cmdshell @cmd
SET @cmd = 'echo \PhysicalDisk(_Total)\Current Disk Queue Length >>' + @path INSERT INTO #Output EXEC master..xp_cmdshell @cmd
SET @cmd = 'echo \PhysicalDisk(_Total)\Disk Read Bytes/sec >>' + @path INSERT INTO #Output EXEC master..xp_cmdshell @cmd
SET @cmd = 'echo \PhysicalDisk(_Total)\Disk Write Bytes/sec >>' + @path INSERT INTO #Output EXEC master..xp_cmdshell @cmd
SET @cmdshell = 'TYPEPERF -s ' +@server+ ' -cf ' +@path+ ' -si ' +@SI+ ' -sc ' +@SC+ ' -o SQL:' +@DSN+ '!' +@SetName
EXEC master..xp_cmdshell @cmdshell
DROP TABLE #Output
The transaction will take as long as you set in @SI & @SC. Once complete you will have data in the three tables which should now exist in the database you created. You can query the data in what ever fashion you feel is helpful.
The CounterData table contains a row for each counter that is collected at a particular time. There will be a large number of these rows.
The CounterData table defines the following fields:
· GUID: GUID for this data set. Use this key to join with the DisplayToID table.
· CounterID: Identifies the counter. Use this key to join with the CounterDetails table.
· RecordIndex: The sample index for a specific counter identifier and collection GUID. The value increases for each successive sample in this log file.
· CounterDateTime: The time the collection was started, in UTC time.
· CounterValue: The formatted value of the counter. This value may be zero for the first record if the counter requires two sample to compute a displayable value.
· FirstValueA: Combine this 32-bit value with the value of FirstValueB to create the FirstValuemember ofPDH_RAW_COUNTER. FirstValueA contains the low order bits.
· FirstValueB: Combine this 32-bit value with the value of FirstValueA to create the FirstValuemember ofPDH_RAW_COUNTER. FirstValueB contains the high order bits.
· SecondValueA: Combine this 32-bit value with the value of SecondValueB to create theSecondValuemember of PDH_RAW_COUNTER. SecondValueA contains the low order bits.
· SecondValueB: Combine this 32-bit value with the value of SecondValueA to create theSecondValuemember of PDH_RAW_COUNTER. SecondValueB contains the high order bits.
Here is a little sample script I created to query the performance data. I added some variables to allow for filtering.
USE perfmoncollector
DECLARE @Server VARCHAR(50),@Object VARCHAR(50),@Counter VARCHAR(50),@TimeZone VARCHAR(50),@SetName VARCHAR(50), @SQL VARCHAR(MAX), @HardwarePerf BIT,
@SQLPerf BIT, @ProcessPerf BIT
----------------------
SET @Server = ''
SET @Object = ''
SET @Counter = ''
SET @TimeZone = ''
SET @SetName = ''
--OR
SET @HardwarePerf = ''
SET @SQLPerf = ''
SET @ProcessPerf = ''
------------------------
--SELECT DISTINCT(Objectname) as ObjectName FROM PerfmonCollector.dbo.CounterDetails
--SELECT DISTINCT(countername) as Countername FROM PerfmonCollector.dbo.CounterDetails
--SELECT DisplayString, LogStartTime, LogStopTime FROM displaytoid
SELECT
DisplayString
,MachineName
,counterdatetime
,objectname
,countername
,countervalue
,TimeZoneName
FROM counterdata cd
JOIN counterdetails cd1 ON cd1.counterid = cd.counterid
JOIN displaytoid dtid on dtid.guid = cd.guid
WHERE (@Server = '' OR MachineName = @Server)
AND (@Object = '' OR ObjectName = @Object)
AND (@Counter = '' OR CounterName = @Counter)
AND (@Timezone = '' OR TimeZoneName = @Timezone)
AND (@SetName = '' OR DisplayString = @SetName)
order by objectname, countername, counterdatetime asc
Enjoy
-B
Thursday, January 20, 2011
Database Growth Rate Script
I developed this script to give me the growth rates of any specified database. Plug in the database name and the date range (or leave date range blank for whole span of time available) and run. The database sizes are based of entries in msdb.dbo.backupset & msdb.dbo.backupfile...
SQL BELOW...
SET NOCOUNT on --------------------------Declaring Variables------------------------------ DECLARE @dbname VARCHAR(50) ,@startsize INT ,@endsize INT ,@increase INT ,@datespan DECIMAL(10,2) ,@perday DECIMAL(10,2) ,@permonth DECIMAL(10,2) ,@peryear DECIMAL(10,2) ,@sql VARCHAR(MAX) ,@Database VARCHAR(50) ,@Startdate DATETIME ,@Enddate DATETIME ----------------------Specify Database and DateRange-------------------------- SET @Database = '' --Set Specific Database or leave blank for all Databases SET @Startdate = '' SET @Enddate = '' --Set Date Range or Leave both blank (YYY-MM-DD) ----------------------------Declaring Variables------------------------------- IF @Database <> '' BEGIN IF @Startdate = '' SET @Startdate = '1901-01-01' IF @Enddate = '' SET @Enddate = '2999-01-01' PRINT 'Growth rate based on historical data in msdb.dbo.backupfile Table...' PRINT CHAR(13) SET @startsize = (SELECT TOP 1 CONVERT(INT, buf.backup_size/1048576) FROM msdb.dbo.backupfile buf JOIN msdb.dbo.backupset bus ON bus.backup_set_id = buf.backup_set_id WHERE database_name = @database AND TYPE = 'D' AND backup_start_date between @startdate and @enddate ORDER BY CONVERT(CHAR, backup_start_date, 111) ASC) SET @endsize = (SELECT TOP 1 CONVERT(INT, buf.backup_size/1048576) FROM msdb.dbo.backupfile buf JOIN msdb.dbo.backupset bus ON bus.backup_set_id = buf.backup_set_id WHERE database_name = @database AND TYPE = 'D' AND backup_start_date between @startdate and @enddate ORDER BY CONVERT(CHAR, backup_start_date, 111) DESC) SET @datespan = (SELECT COUNT(DISTINCT( backup_start_date )) FROM msdb.dbo.backupfile buf JOIN msdb.dbo.backupset bus ON bus.backup_set_id = buf.backup_set_id WHERE database_name = @database AND TYPE = 'D' AND backup_start_date between @startdate and @enddate) SET @increase = @endsize - @startsize SET @perday = @increase / @datespan SET @permonth = @perday * 30 SET @peryear = @perday * 365 -------------------Retrieving Growth Stats For Specified DB------------------ SELECT TOP 1 CONVERT(VARCHAR(30), database_name)AS 'BackupFile.bak' ,(SELECT TOP 1 CONVERT(CHAR, backup_start_date, 111) FROM msdb.dbo.backupfile buf JOIN msdb.dbo.backupset bus ON bus.backup_set_id = buf.backup_set_id WHERE database_name = @database AND TYPE = 'D' AND backup_start_date between @startdate and @enddate ORDER BY CONVERT(CHAR, backup_start_date, 111) ASC) AS 'StartDate' ,(SELECT TOP 1 CONVERT(INT, buf.backup_size/1048576) FROM msdb.dbo.backupfile buf JOIN msdb.dbo.backupset bus ON bus.backup_set_id = buf.backup_set_id WHERE database_name = @database AND TYPE = 'D' AND backup_start_date between @startdate and @enddate ORDER BY CONVERT(CHAR, backup_start_date, 111) ASC) AS 'StartSize_MB' ,(SELECT TOP 1 CONVERT(CHAR, backup_start_date, 111) FROM msdb.dbo.backupfile buf JOIN msdb.dbo.backupset bus ON bus.backup_set_id = buf.backup_set_id WHERE database_name = @database AND TYPE = 'D' AND backup_start_date between @startdate and @enddate ORDER BY CONVERT(CHAR, backup_start_date, 111) DESC) AS 'EndDate' ,(SELECT TOP 1 CONVERT(INT, buf.backup_size/1048576) FROM msdb.dbo.backupfile buf JOIN msdb.dbo.backupset bus ON bus.backup_set_id = buf.backup_set_id WHERE database_name = @database AND TYPE = 'D' AND backup_start_date between @startdate and @enddate ORDER BY CONVERT(CHAR, backup_start_date, 111) DESC) AS 'EndSize_MB' ,(SELECT @datespan) AS 'DateSpan_Days' ,(SELECT @increase)AS 'Total_Increase_MB' ,(SELECT @perday)AS 'MB_Per_Day' ,(SELECT @permonth) AS 'MB_Per_Month' ,(SELECT @peryear)AS 'MB_Per_Year' FROM msdb.dbo.backupfile buf JOIN msdb.dbo.backupset bus ON bus.backup_set_id = buf.backup_set_id WHERE database_name = @database PRINT CHAR(13) END --------------------------DB Cycle Through for All DB-------------------------- IF @Database = '' BEGIN SELECT DISTINCT( database_name ) INTO #dbnametemp FROM msdb.dbo.backupset WHERE database_name <> 'msdb' ----------------------------Create Temp #AllDatabases------------------------------- CREATE TABLE #AllDatabases ( database_name VARCHAR(50) ,DateSpan_Days DECIMAL(10,2) , Total_Increase_MB DECIMAL (10,2) , MB_Per_Day DECIMAL(10,2) , MB_Per_Month DECIMAL (10,2) , MB_Per_Year DECIMAL (10,2) ) -----------------------------------Cursor Start------------------------------------- DECLARE db_curs CURSOR FOR (SELECT database_name FROM #dbnametemp) OPEN db_curs FETCH NEXT FROM db_curs INTO @database WHILE @@FETCH_STATUS = 0 BEGIN ------------------------------------Define Variable------------------------------------- IF @Startdate = '' SET @Startdate = '1901-01-01' IF @Enddate = '' SET @Enddate = '2099-01-01' SET @startsize = (SELECT TOP 1 CONVERT(INT, buf.backup_size/1048576) FROM msdb.dbo.backupfile buf JOIN msdb.dbo.backupset bus ON bus.backup_set_id = buf.backup_set_id WHERE database_name = @database AND TYPE = 'D' AND backup_start_date between @startdate and @enddate ORDER BY CONVERT(CHAR, backup_start_date, 111) ASC) SET @endsize = (SELECT TOP 1 CONVERT(INT, buf.backup_size/1048576) FROM msdb.dbo.backupfile buf JOIN msdb.dbo.backupset bus ON bus.backup_set_id = buf.backup_set_id WHERE database_name = @database AND TYPE = 'D' AND backup_start_date between @startdate and @enddate ORDER BY CONVERT(CHAR, backup_start_date, 111) DESC) SET @datespan = (SELECT COUNT(DISTINCT( backup_start_date )) FROM msdb.dbo.backupfile buf JOIN msdb.dbo.backupset bus ON bus.backup_set_id = buf.backup_set_id WHERE database_name = @database AND TYPE = 'D' AND backup_start_date between @startdate and @enddate) SET @increase = @endsize - @startsize SET @perday = @increase / @datespan SET @permonth = @perday * 30 SET @peryear = @perday * 365 -----------------------------Insert Into #AllDatabases---------------------------------- INSERT INTO #AllDatabases ( database_name ,DateSpan_Days , Total_Increase_MB , MB_Per_Day , MB_Per_Month , MB_Per_Year ) SELECT TOP 1 CONVERT(VARCHAR(30), database_name)AS 'database_name' ,(SELECT @datespan) AS 'DateSpan_Days' ,(SELECT @increase)AS 'Total_Increase_MB' ,(SELECT @perday)AS 'MB_Per_Day' ,(SELECT @permonth) AS 'MB_Per_Month' ,(SELECT @peryear)AS 'MB_Per_Year' FROM msdb.dbo.backupfile buf JOIN msdb.dbo.backupset bus ON bus.backup_set_id = buf.backup_set_id WHERE database_name = @database FETCH NEXT FROM db_curs INTO @database END CLOSE db_curs DEALLOCATE db_curs --------------------------Insert Totals in #AllDatabases----------------------- INSERT INTO #AllDatabases ( database_name ,DateSpan_Days , Total_Increase_MB , MB_Per_Day , MB_Per_Month , MB_Per_Year ) SELECT (Select 'TOTALDATA') ,(Select top 1 DateSpan_Days FROM #AllDatabases) ,(Select SUM(Total_Increase_MB) FROM #AllDatabases) ,(Select SUM(MB_Per_Day) FROM #AllDatabases) ,(Select SUM(MB_Per_Month) FROM #AllDatabases) ,(Select SUM(MB_Per_Year) FROM #AllDatabases) --------------------------Select On #AllDatabases----------------------- PRINT 'Growth rate based on historical data in msdb.dbo.backupfile Table...' PRINT CHAR(13) SELECT database_name as 'BackupFile.bak' --,database_name --,DateSpan_Days --,Total_Increase_MB ,MB_Per_Day ,MB_Per_Month ,MB_Per_Year FROM #AllDatabases DROP TABLE #AllDatabases DROP TABLE #dbnametemp END
Subscribe to:
Posts (Atom)