Wednesday, February 9, 2011

Change Agent Job or Maintenance Plan Ownership

I'm sure you've all seen a SQL Server Agent Job, or Maintenance Plan owned by the person who created the job (ie DOMAIN\JaneDoe).  Not a big deal, I suppose, if that person is never going to leave the company, but... If that login is disabled, those jobs are going to fail.  You can use both of these in the msdb database, to change the ownership of your Agent Jobs, and your Maintenance plans.

    --Use this to alter SQL Server Agent Job owner:
    EXEC msdb..sp_update_job 
     @job_name = 'job_name', 
     @owner_login_name ='login' 

Of course, you need to be sure your new login has adequate permissions.

      --Use this to alter the Maintenance Plan owner to 'sa':
    UPDATE msdb..sysssispackages
    SET [ownersid]=0x01
    WHERE [name]='YourMaintPlanName'
You don't need to worry about changing the ownersid value here, because the SID for 'sa' is always 0x01. 

I typically use 'sa' for my Agent Job and Maintenance Plan ownership.  Another option would be the SQL Service account login.  Any other generic login would be fine, too, as long as it has the appropriate privileges, locally and on the network.  Just keep in mind your business security policies.

Sunday, February 6, 2011

Database Data & Log File Location

Whenever I take on a new project, I always have to go in and do a little digging, to find all of the data and log files (among other things).  I rarely use the default C:\Program Files\ location for my database files, but you know... there are many different ways to get it done.

Use this to return the default data and log file directories for an instance, regardless of  existing databases.  

 DECLARE @SmoDefaultFile NVARCHAR(1000)
 EXEC master..xp_instance_regread N'HKEY_LOCAL_MACHINE',  
 N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', 
 @SmoDefaultFile OUTPUT

 DECLARE @SmoDefaultLog NVARCHAR(1000)
 EXEC master..xp_instance_regread N'HKEY_LOCAL_MACHINE', 
 N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', 
 @SmoDefaultLog OUTPUT

    ISNULL(@SmoDefaultFile,N'') [Default MDF Location],
    ISNULL(@SmoDefaultLog,N'') [Default LDF Location]

Use this to return details specific to the the databases on the server, using sp_msforeachdb with a select against sys.database_files:

  IF OBJECT_ID('DatabaseFiles') IS NULL
    SELECT TOP 0 * INTO DatabaseFiles
    FROM sys.database_files

    ALTER TABLE DatabaseFiles

  EXECUTE sp_msforeachdb 'INSERT INTO DatabaseFiles SELECT *, 
  GETDATE() FROM [?].sys.database_files'

  SELECT * FROM dbo.DatabaseFiles

And don't forget this one, if you want to return your database file sizes back in MB, rather than KB:   Database File Size (MB)

Start SQL Server Agent job remotely

Sometimes it is necessary to perform administrative tasks on other SQL Servers, remotely.  As your inventory grows, using Remote Desktop to connect to every one of them can become a little inconvenient.  I administer my instances remotely, as needed.  For example, the statement below can be used to start a SQL Server Agent job remotely, on another SQL Server:

  SET @sql  = ''

  DECLARE @SystemName VARCHAR(50)
  DECLARE @JobName     VARCHAR(50)
  DECLARE @StepName    VARCHAR(50)

  SET @SystemName = 'Other Server Name'
  SET @JobName = 'Agent Job Name'

  SET @sql = '' + @SystemName + '.msdb..sp_start_job @job_name = 
  ''' + @JobName + ''''

  --PRINT 'EXEC '+ @sql
  EXEC (@sql)

Why don't you comment out the EXEC, and un-comment the PRINT before running, just to see how things look.

Please don't forget, you can also use SQL Server Configuration Manager to connect to other servers at a higher level, and administer the services remotely.  See this topic in BOL:  'How to: Connect to Another Computer (SQL Server Configuration Manager)'  .

Saturday, February 5, 2011

Aggregate performance statistics

I mentioned in another post how impressed I am with the new DMV's.  The sys.dm_exec_query_stats view is very helpful for determining what queries are running long, or consuming a bit heavily on the resources.  Per BOL, this view returns aggregate performance statistics for cached query plans.  I've posted some examples below.  Let me know if you have any questions, or any examples of your own to share.

This one will return the TOP 10 most CPU heavy statements being run:

  SUBSTRING(b.text, (a.statement_start_offset/2) + 1,((CASE  
   statement_end_offset WHEN -1 THEN DATALENGTH(b.text) 
   ELSE a.statement_end_offset END - a.statement_start_offset)/2) + 1) AS statement_text,
  total_worker_time [cpu_time]
FROM sys.dm_exec_query_stats a  
CROSS APPLY sys.dm_exec_sql_text (a.sql_handle) b  
CROSS APPLY sys.dm_exec_query_plan (a.plan_handle) c  
total_worker_time DESC

This one will show you the TOP 5 procedures, based on which ones have the greatest execution count:

SELECT TOP 5        
b.text AS 'Procedure Name',
a.execution_count AS 'Execution Count',
a.execution_count/DATEDIFF(SECOND, a.creation_time, GETDATE()) AS 'Calls/Second',
a.total_worker_time/a.execution_count AS 'Avg CPU Time',
a.total_worker_time AS 'Total CPU Time',
a.total_elapsed_time/a.execution_count AS 'Avg Elapsed Time',
a.max_logical_reads [MAX Logical Reads],
a.max_logical_writes [MAX Logical Writes],
a.total_physical_reads [Total Physical Reads],
DATEDIFF(MINUTE, a.creation_time, GETDATE()) AS 'Cached How Long'  
  sys.dm_exec_query_stats a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b  
a.execution_count DESC

There are many other statistics you could retrieve, such as the top I/O bound statements, or even which ones are recompiled more than the others.  This is one that I use to see which queries or procedures are performing slowly:

total_elapsed_time / execution_count avg_elapsed_time,
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,((
CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) 
    ELSE qs.statement_end_offset END -   
  qs.statement_start_offset)/2) + 1) AS statement_text
sys.dm_exec_query_stats AS qs 
  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
total_elapsed_time / execution_count DESC;

Important to remember, it can only retrieve what is cached.

Add a DEFAULT Constraint

On many occasions over the years, I've enabled a DATETIME attribute on a table, but allowed SQL to populate it for me, with a GETDATE() DEFAULT constraint.  This can be very helpful for monitoring application data flow, and for troubleshooting or debugging.  To know exactly when a record was written, is a very good thing, I can assure you.

You may already have a date field, but there is no constraint.  Just use this to add the constraint to your table:  

   ALTER TABLE [dbo].[YourTable] 
   ADD CONSTRAINT [df_YourTable_DateField]
   DEFAULT (GETDATE()) FOR [DateField];

Or, this one can be used to both add the new field, and enable the constraint:

   ALTER TABLE [dbo].[YourTable]
   CONSTRAINT [df_YourTable_InsertTime] DEFAULT {GETDATE());

I usually use 'InsertTime' or 'DateCreated' to name attributes like this, because I think it very intuitively suggests what the value is used for. 

Friday, February 4, 2011

How to uninstall SQL Server Reporting Services (SSRS)

Do you know how to remove SSRS, without uninstalling other components of SQL Server?  Easy sneasy, check it out:
  1. Go to your Control Panel Add/Remove, or Programs/Uninstall a program
  2. Double-click 'Microsoft SQL Server 2008 R2'
  3. In the next dialog next, you will see 'Add', 'Repair' and 'Remove'.  You will choose 'Remove', and then 'OK'.
  4. The next dialog is 'Select Instance', you will select the right one, and click 'Next'.
  5. The next dialog is 'Select Features', you will ONLY select 'Reporting Services', and then 'Next'.  This one is important, so I've copied it here:

The uninstall begins now, and when it is done you will see this:
        'Your SQL Server 2008 R2 removal completed successfully'.

You will still need to drop, or remove your ReportServer and ReportServerTempDB databases.

Can this be done intra-day, while the server is in use?  Yes.
Does this require a reboot, or service restart?  No.

Please let me know if you have any problems or questions.

Wednesday, February 2, 2011

SQL Server -- which objects reside on which filegroups?

This is a very handy statement joining sys.filegroups with sys.indexes, listing the filegroups that each of your objects reside upon.  Note, you can remove the o.type = 'U' to review the data file location for all objects:
o.[name] [TableName], 
i.[name] [IndexName]
f.[name] [FileGroup]
sys.indexes i INNER JOIN sys.filegroups f
       ON i.data_space_id = f.data_space_id 
      INNER JOIN sys.all_objects o
ON i.[object_id] = o.[object_id]
i.data_space_id = f.data_space_id
AND o.type = 'U' -- Remove this to see other objects
AND <> 'sysdiagrams'
AND <> 'dtproperties'

These are some of the results from my own DBA database:

Missing IDENTITY values

Many times we'll find holes in the sequence of our IDENTITY values.  This can be caused by the normal deletion of records, or it can happen if an insert transaction failed to complete, and was rolled back.  This statement will help you identify the missing values within your  table's IDENTITY sequence:


  SELECT @MaxID = [ID] FROM dbo.YourTable

  SET @ID = 1
  WHILE @ID <= @MaxID
          SELECT 'X' FROM dbo.YourTable
          WHERE [ID] = @ID)

     INSERT INTO @MissingIDs ([ID] )
     VALUES ( @ID )

     SET @ID = @ID + 1

Review them all, or maybe just return the MIN and the MAX of the missing IDs that have been found:

  SELECT [ID] FROM @MissingIDs 
  SELECT MIN(ID) MinAvailID FROM @MissingIDs
  SELECT MAX(ID)+1 MaxAvailID FROM dbo.YourTable


Often we need to reseed, or reset the IDENTITY value for our tables.  This is a quick way to reseed your 'Customer' table, so the next record added starts at 100:
   DBCC CHECKIDENT('Customers' , RESEED, 100);

Remember, if your table has existing records, the next IDENTITY value will be 101.

Or, if you want to report the current IDENTITY, but you don't want to ALTER it, try this:

Maybe your table has been around forever, has been deleted from many times, and you just want to return the original seed value that was used with the table was created:
  SELECT IDENT_SEED('Customers') AS Identity_Seed;

Lastly, use this to return the increment value of your IDENTITY column:
  SELECT IDENT_INCR('Customers') AS Identity_Increment;

These are just a few tips I can offer on our IDENTITY values.  Definitely check BOL for a much greater explanation on all of the above, and more.