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

 SELECT 
    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
  BEGIN
    SELECT TOP 0 * INTO DatabaseFiles
    FROM sys.database_files


    ALTER TABLE DatabaseFiles
    ADD CreationDate DATETIME DEFAULT(GETDATE())
  END


  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:

  DECLARE @sql VARCHAR(MAX)
  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:

SELECT TOP 10  
  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,
  c.query_plan,
  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  
ORDER BY 
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'  
FROM
  sys.dm_exec_query_stats a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b  
WHERE 
b.dbid = DB_ID() -- <<<ONLY FOR THE CURRENT DATABASE  
ORDER BY 
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:

SELECT 
last_execution_time,
execution_count, 
total_physical_reads,
total_logical_reads,
total_logical_writes,
total_worker_time, 
total_elapsed_time, 
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
FROM 
sys.dm_exec_query_stats AS qs 
  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY 
total_elapsed_time / execution_count DESC;

NOTES:   
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]
   ADD InsertTime DATETIME NOT NULL 
   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.


FAQ
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:
  SELECT 
o.[name] [TableName], 
i.[name] [IndexName]
f.[name] [FileGroup]
  FROM 
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]
  WHERE 
i.data_space_id = f.data_space_id
AND o.type = 'U' -- Remove this to see other objects
AND o.name <> 'sysdiagrams'
AND o.name <> 'dtproperties'
  ORDER BY 
o.name

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:

  DECLARE @ID INT
  DECLARE @MaxID INT
  DECLARE @MissingIDs TABLE ([ID] INT )

  SELECT @MaxID = [ID] FROM dbo.YourTable


  SET @ID = 1
  WHILE @ID <= @MaxID
  BEGIN
     IF NOT EXISTS (
          SELECT 'X' FROM dbo.YourTable
          WHERE [ID] = @ID)


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


     SET @ID = @ID + 1
  END

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

IDENTITY Values

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:
  DBCC CHECKIDENT ('Customers', NORESEED);


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.