RebornDBA

Using PowerPivot to read my perfmon Data

2009-11-26T14:34:45Z

Last week I attended SQL Bits V and I can say it was a great experience it is definitely a must go event, if you missed it, keep you eyes open to SQL Bits VI. Anyway, on the Friday sessions we had Donald Farmer talking about PowerPivot for Excel/SharePoint 2010 and also the always energetic Brent Ozar talking about the use of Perfmon to monitor proactively our DB’s (I recommend his blog is full of useful stuff for all levels) and during the chat it hit me lest mix the two.

I like the idea of capturing key perfmon indicators as detailed by Mr Ozar but instead of dumping them into a csv to then import into excel and use the excel data mining tool I do a dump to a sql db. I then create a view on top of the 3 tables generated by perfmon and do some magic with PowerPivot. I have to say that the exercise took me around 2 days to capture the required data, setting up VM machine with SQL Server, Office 2010 and PowerPivot.

The actual PowerPivot work was under an hour, once you get use to the tool everything is simple adding extra columns on the fly using expressions etc…

The nice thing about having perfmon dumping data to the sql db is that the only thing I need to do now is refresh the data feed for PowerPivot and Bob’s your uncle!

I got the following result:

Not bad for an hours dev eh!!

Ok so here is how I did it:

1. On Performance Monitor Logs setup a job to grab the following (Brent Ozar has great blog on this):

Memory – Available MBytes
Paging File – % Usage
Physical Disk – % Disk Time
Physical Disk – Avg. Disk Queue Length
Physical Disk – Avg. Disk sec/Read
Physical Disk – Avg. Disk sec/Write
Physical Disk – Disk Reads/sec
Physical Disk – Disk Writes/sec
Processor – % Processor Time
SQLServer:Buffer Manager – Buffer cache hit ratio
SQLServer:Buffer Manager – Page life expectancy
SQLServer:General Statistics – User Connections
SQLServer:Memory Manager – Memory Grants Pending
System – Processor Queue Length

2. Set up the dump to a db (Note you will need to do a run as on the server with an account with the correct access)

3. Create a view on top of the Perfmon Tables with the following code:

          1: CREATE VIEW [DBO].[PERFMONDATA]

         2: AS

         3: SELECT          

         4:               DOD.DISPLAYSTRING

         5:             , CDT.MACHINENAME

         6:             , CDT.OBJECTNAME

         7:             , CDT.COUNTERNAME

         8:             , CDT.INSTANCENAME

         9:             , CDA.COUNTERDATETIME

        10:             , CDA.COUNTERVALUE

        11: FROM         DBO.COUNTERDATA AS CDA 

        12:                     INNER JOIN DBO.COUNTERDETAILS AS CDT

        13:                             ON CDA.COUNTERID = CDT.COUNTERID 

        14:                     INNER JOIN DBO.DISPLAYTOID AS DOD

        15:                             ON CDA.GUID = DOD.GUID

4. Fire up Excel 2010 and open the PowerPivot Plugin

5. Add Connection String to the instance hosting your Perfmon Db and pull the view created earlier

6. I used the expressions SECOND,MINUTE,HOUR,DAY,MONTH and YEAR to calculate those value on the fly, so you end up with something like this:

7. Use Pivot functionality from Excel and add a couple of sliders and you are Done.

I hope you found this useful

Cheers,

@dcastrogavino

Where are my database backups?

2009-11-26T14:34:16Z

Don’t you ever ask yourself, are my maintenance back up plans working?

Today I’ve been looking at the database backup history information of my servers to make sure that I have my back covered. So using our loved msdb system database tables I have done a couple of useful script which I have schedule as jobs to email the results on a regular basis.

The main tables I’ve used are:

MSDB.DBO.BACKUPMEDIAFAMILY - http://msdn.microsoft.com/en-us/library/ms190284(SQL.90).aspx

MSDB.DBO.BACKUPSET - http://msdn.microsoft.com/en-us/library/ms186299(SQL.90).aspx

My 2 simple scripts are:

1. Identify Database Backups for all db’s on the last 7 days:

        1: SELECT 

       2:         CONVERT(VARCHAR(64), SERVERPROPERTY('SERVERNAME')) AS SERVER,

       3:         BST.DATABASE_NAME, 

       4:         BST.BACKUP_START_DATE, 

       5:         BST.BACKUP_FINISH_DATE,

       6:         BST.EXPIRATION_DATE,

       7:         CASE BST.TYPE 

       8:                 WHEN 'D' THEN 'FULL'  

       9:                 WHEN 'I' THEN 'DIFFERENTIAL' 

      10:                 WHEN 'L' THEN 'LOG'

      11:                 WHEN 'F' THEN 'FILE / FILEGROUP'

      12:                 WHEN 'G' THEN 'DIFFERENTIAL FILE'

      13:                 WHEN 'P' THEN 'PARTIAL'

      14:                 WHEN 'Q' THEN 'DIFFERENTIAL PARTIAL'

      15:         END AS TYPE, 

      16:         BST.BACKUP_SIZE, 

      17:         BMF.LOGICAL_DEVICE_NAME, 

      18:         BMF.PHYSICAL_DEVICE_NAME,  

      19:         BST.NAME AS BACKUPSET_NAME,

      20:         BST.DESCRIPTION

      21: FROM   MSDB.DBO.BACKUPMEDIAFAMILY  AS BMF

      22:             INNER JOIN MSDB.DBO.BACKUPSET AS BST 

      23:                 ON BMF.MEDIA_SET_ID = BST.MEDIA_SET_ID 

      24: WHERE  BST.BACKUP_START_DATE >= GETDATE() - 7 

      25: ORDER BY 

      26:    BST.DATABASE_NAME,

      27:    BST.BACKUP_FINISH_DATE

2. Identify the most recent full (type D) Backup for each DB with detail information about the backup:

       1: SELECT 

       2:        BMFBST.SERVERNAME, 

       3:        BMFBST.LAST_BACKUP_DATE, 

       4:        BMFBSTD.BACKUP_START_DATE, 

       5:        BMFBSTD.EXPIRATION_DATE,

       6:        BMFBSTD.BACKUP_SIZE, 

       7:        BMFBSTD.LOGICAL_DEVICE_NAME, 

       8:        BMFBSTD.PHYSICAL_DEVICE_NAME,  

       9:        BMFBSTD.BACKUPSET_NAME,

       BMFBSTD.DESCRIPTION

FROM

   (

/*

    List of the most recent full backups for each database    

*/

    SELECT 

         CONVERT(VARCHAR(64),SERVERPROPERTY('SERVERNAME')) AS SERVERNAME

        ,BST.DATABASE_NAME

        ,MAX(BST.BACKUP_FINISH_DATE) AS LAST_BACKUP_DATE

    FROM   MSDB.DBO.BACKUPMEDIAFAMILY BMF

           INNER JOIN MSDB.DBO.BACKUPSET BST

                ON BMF.MEDIA_SET_ID = BST.MEDIA_SET_ID 

    WHERE  BST.TYPE = 'D'

    GROUP BY  BST.DATABASE_NAME 

   ) AS BMFBST

   LEFT JOIN 

   (

/*

    Detail information about back to join with the previous list

*/

    SELECT  

       CONVERT(VARCHAR(64), SERVERPROPERTY('SERVERNAME')) AS SERVER,

       BST.DATABASE_NAME, 

       BST.BACKUP_START_DATE, 

       BST.BACKUP_FINISH_DATE,

       BST.EXPIRATION_DATE,

       BST.BACKUP_SIZE, 

       BMF.LOGICAL_DEVICE_NAME, 

       BMF.PHYSICAL_DEVICE_NAME,  

       BST.NAME AS BACKUPSET_NAME,

       BST.DESCRIPTION

    FROM   MSDB.DBO.BACKUPMEDIAFAMILY AS BMF

            INNER JOIN MSDB.DBO.BACKUPSET BST

                ON BMF.MEDIA_SET_ID = BST.MEDIA_SET_ID 

        WHERE  BST.TYPE = 'D'

   ) AS BMFBSTD

   ON BMFBST.SERVERNAME = BMFBSTD.[SERVER] AND 

      BMFBST.DATABASE_NAME = BMFBSTD.[DATABASE_NAME] AND 

      BMFBST.LAST_BACKUP_DATE = BMFBSTD.[BACKUP_FINISH_DATE]

ORDER BY 

   BMFBST.DATABASE_NAME

I hope you find these useful,

Cheers,

@dcastrogavino

Technorati Tags: sql server,database backup,t-sql

My first issue as a reborn DBA: Installing SQL Server 2005 SP1 on a 6 core machine. How difficult could this be?

2009-11-11T10:52:24Z

One of my tasks today was to do an installation of a STD version of SQL 2005 on a 6 core machine. Unfortunately, the only image I had was a SP1 so I started with my prep as normal.
-    Get image across to the server
-    Download SP3
-    Start install
At this point I think ... mmm... How difficult could this be?, it is a simple install and I’ll be done in less than an hour. How wrong was I!... as the installation setup gets to the point where it tries to start SQL Server it returned a useful message:

Product: Microsoft SQL Server 2005 (64-bit) -- Error 29503. The SQL Server service failed to start. For more information, see the SQL Server Books Online topics, "How to: View SQL Server 2005 Setup Log Files" and "Starting SQL Server Manually."

I said ok, I’ll try to start the service manually, and I then get another useful message

"Error 1053: The service did not respond to the start or control request in a timely fashion"

I started to think that this must be a permission issue, so I tried to start the service with my admin account rather than the service account that I had used for the installation and the same error appeared.
I then go to the Event Viewer and I see the following error:

“Faulting application sqlservr.exe, version 2005.90.1399.0, faulting module sqlservr.exe, version 2005.90.1399.0, fault address 0x0000000000b323f0.”

So at this point I try to do a tintenet search and I get two results of which the only really useful one was:

http://blogs.msdn.com/psssql/archive/2008/07/23/sql-server-2005-encounters-exception-during-install-when-system-has-odd-number-of-processors-or-logical-processors-per-core.aspx

This enlightened me with the issue of Installing SQL Server 2005 sp1 with an odd number of logical processors which is corrected on the SP2. The workaround that Bob Dorr proposed sounded a bit aggressive as I don’t like the idea of striping dll’s from sp2 to overwrite the existing ones and the idea of being on a production unsupported situation is something that I’d rather not risk.

So you ask, how did I solve the issue? Well, on the setup of the actual server I disabled one of the processors to leave the box running with 2 logical processors. I restarted the installation and applied service pack in my case SP3. Once I was happy I returned the processors count the original one and Bob’s your uncle!

I hope you found this useful.

Regards,

David Castro