RebornDBA

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

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,

  10:        BMFBSTD.DESCRIPTION

  11: FROM

  12:    (

  13: /*

  14:     List of the most recent full backups for each database    

  15: */

  16:     SELECT 

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

  18:         ,BST.DATABASE_NAME

  19:         ,MAX(BST.BACKUP_FINISH_DATE) AS LAST_BACKUP_DATE

  20:     FROM   MSDB.DBO.BACKUPMEDIAFAMILY BMF

  21:            INNER JOIN MSDB.DBO.BACKUPSET BST

  22:                 ON BMF.MEDIA_SET_ID = BST.MEDIA_SET_ID 

  23:     WHERE  BST.TYPE = 'D'

  24:     GROUP BY  BST.DATABASE_NAME 

  25:    ) AS BMFBST

  26:    LEFT JOIN 

  27:    (

  28: /*

  29:     Detail information about back to join with the previous list

  30: */

  31:     SELECT  

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

  33:        BST.DATABASE_NAME, 

  34:        BST.BACKUP_START_DATE, 

  35:        BST.BACKUP_FINISH_DATE,

  36:        BST.EXPIRATION_DATE,

  37:        BST.BACKUP_SIZE, 

  38:        BMF.LOGICAL_DEVICE_NAME, 

  39:        BMF.PHYSICAL_DEVICE_NAME,  

  40:        BST.NAME AS BACKUPSET_NAME,

  41:        BST.DESCRIPTION

  42:     FROM   MSDB.DBO.BACKUPMEDIAFAMILY AS BMF

  43:             INNER JOIN MSDB.DBO.BACKUPSET BST

  44:                 ON BMF.MEDIA_SET_ID = BST.MEDIA_SET_ID 

  45:         WHERE  BST.TYPE = 'D'

  46:    ) AS BMFBSTD

  47:    ON BMFBST.SERVERNAME = BMFBSTD.[SERVER] AND 

  48:       BMFBST.DATABASE_NAME = BMFBSTD.[DATABASE_NAME] AND 

  49:       BMFBST.LAST_BACKUP_DATE = BMFBSTD.[BACKUP_FINISH_DATE]

  50: ORDER BY 

  51:    BMFBST.DATABASE_NAME

I hope you find these useful,

Cheers,

@dcastrogavino

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