Friday, February 25, 2011

How to run a stored procedure at SQL Server start-up

This is actually quite simple. There is 'startup' option that you can set to the procedure.

There are a few limitations though:

- sp must reside in the [master] database

- it's owner must be dbo

- it mustn't have any input or output parameters

Note that each stored procedure run at start up takes up one worker thread until finished. So if you want to run multiple sps at runtime and parallelism doesn't matter create one sp that executes all others.

sample code

USE master;
GO
-- first set the server to show advanced options
EXEC sp_configure 'show advanced option', '1';
RECONFIGURE
-- then set the scan for startup procs to 1
EXEC sp_configure 'scan for startup procs', '1';
RECONFIGURE
IF OBJECT_ID('spTest') IS NOT NULL
    DROP PROC spTest
GO
-- crate a test stored procedure
CREATE PROC spTest
AS
-- just create a sample database
EXEC('CREATE database db1')
GO
-- set it to run at sql server start-up
exec sp_procoption N'spTest', 'startup', 'on'

Wednesday, February 23, 2011

alternate to IF EXISTS ..UPDATE ELSE ..INSERT

On some occasions we need to update an entry in a table in case there is an entry otherwise we need to insert that data into the table. The easiest way to do this is something like this

IF EXISTS ( SELECT customer_id
            FROM   dbo.customer_comments
            WHERE  customer_id = @customerId ) 
	UPDATE  dbo.customer_comments
	SET     customer_comment = @comment +  'something here' 
	WHERE   customer_id = @customerId
ELSE 
	INSERT  INTO dbo.customer_comments( customer_id, customer_comment )
	VALUES  ( @customerId, @comment )


 



If you check the code you can see that for an update, sql server has to go thru the table two times, one for checking whether an entry exists and if exists, it still need to find the same location again to do the UPDATE



Now check the following code,



UPDATE dbo.customer_comments 
SET customer_comment= @comment + 'something here' 
WHERE customer_id = @customerId
IF @@ROWCOUNT = 0 
      INSERT INTO dbo.customer_comments (customer_id, customer_comment) 
      VALUES (@customerId, @comment)


It directly try to update the table, and checks for the value of @@ROWCOUNT before it performs the INSERT operation; (Remember @@ROWCOUNT holds the number of rows affected by the LAST statement ). This way we will be able to save one table access for performing the UPDATE operation; 



There won’t be any performance difference for the ‘insert’ operation.

Thursday, February 10, 2011

Script to Check and Fix Orphan Users

 

 

-- Script to check the orphan user
EXEC sp_change_users_login 'Report'
--Use below code to fix the Orphan User issue
DECLARE @username varchar(25)
DECLARE fixusers CURSOR 
FOR
SELECT UserName = name FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
ORDER BY name
OPEN fixusers
FETCH NEXT FROM fixusers
INTO @username
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_change_users_login 'update_one', @username, @username
FETCH NEXT FROM fixusers
INTO @username
END
CLOSE fixusers
DEALLOCATE fixusers

DBCC UPDATEUSAGE and sp_updatestats

This may provide some light on the usage of DBCC UPDATEUSAGE and sp_updatestats

DBCC UPDATEUSAGE corrects the rows, used, reserved, and dpages columns of the sysindexes table for tables and clustered indexes. Size information is not maintained for nonclustered indexes.

If there are no inaccuracies in sysindexes, DBCC UPDATEUSAGE returns no data. If inaccuracies are found and corrected and the WITH NO_INFOMSGS option is not used, UPDATEUSAGE returns the rows and columns being updated in sysindexes.

Use UPDATEUSAGE to synchronize space-usage counters. DBCC UPDATEUSAGE can take some time to run on large tables or databases, so it should typically be used only when you suspect incorrect values returned by sp_spaceused. sp_spaceused accepts an optional parameter to run DBCC UPDATEUSAGE before returning space information for the table or index. ( BOL )


What is UPDATE STATISTICS ?

SQL Server keeps statistics about the distribution of the key values in each index and uses these statistics to determine which index(es) to use in query processing. Users can create statistics on nonindexed columns by using the CREATE STATISTICS statement. Query optimization depends on the accuracy of the distribution steps:
- If there is significant change in the key values in the index, rerun UPDATE STATISTICS on that index.
- If a large amount of data in an indexed column has been added, changed, or removed (that is, if the distribution of key values has changed), or the table has been truncated using the TRUNCATE TABLE statement and then repopulated, use UPDATE STATISTICS.

Statistics can be created or updated on tables with computed columns only if the conditions are such that an index can be created on these columns.


Why we need to use UPDATE STATISTICS while we use as the part of SQL Server database maintenance?


If you have a performance issue with a query, the very first step you should take before analyzing it is to update the statistics on all the tables in your database. This is because many query performance issues are due to outdated statistics, and updating them before troubleshooting query performance may save you a lot of time. If the query is still giving you trouble after updating the statistics, then you can begin your troubleshooting.

It is recommended that "Auto update statistics" should be disabled. While we do disable this option, we should have a maintenance plan for this activity in day-to-day basis.

It is recommended that "Auto update statistics" should be disabled. While we do disable this option, we should have a maintenance plan for this activity in day-to-day basis. The best practice says do the UPDATE STATISTICS for all tables and for all databases every night. This is because the auto-update option is less than perfect, and accurate and up-to-date statistics is so important to overall performance of SQL Server.

Of course, if this is not possible because of time constraints, then you will have to perform this step less often, or even not at all, and rely on the auto-update feature. I recommend this has to be done manually.

What does this activity do?

For each table in your database, SQL Server automatically maintains this information on them:
- The number of data pages used by each table.
- The number rows in each table.
- The number of INSERTS, UPDATES, and DELETES that affect the keys of the table since the last statistics update.

Besides the table statistics, SQL Server can maintain statistics on all of the indexes in your database. SQL Server maintains this index statistics information:
- A histogram of the distribution of the data in the first column of the index.
- The densities of all column prefixes.
- The average key length of the index.

SQL Server can also collect the above statistics for any column you specify. This data can be used by the Query Optimizer to make better decision. Column statistics aren't automatically collected unless you tell SQL Server to collect them.

In most cases, you will probably allow SQL Server to update index statistics automatically. This feature can be changed by setting the database option "Auto Update Statistics" to either true of false. Be default, this feature is set to true, which means that index statistics are automatically updated, which is not recommended.

Why it is not recommended is, in some very busy SQL Servers, this feature can interfere with normal daily activity. This is because this feature may start its own, at times when the server is already very busy, which may degrading performance. In these cases, it is often better to turn this feature off and to manually or schedule an update statistics during off-peak time in database usage.


How does SQL Server know when to update statistics?

SQL Server follows a very specific set of rules on when it should update the statistics of an index. Here they are:
- If the number of rows in a table are greater than 6, but less than or equal to 500, then statistics are automatically updated when there have been 500 modifications made.
- If the number of rows in the table are greater than 500, then updates are automatically made when (500 plus 20 percent of the number of rows in the table) have been modified.

If you like, you can check to see how many modifications have been made to a table, and at the same time estimate when an automatic statistics update will occur. If you go to the sysindexes table of the database in question, and look at the rowmodctr column, it will show you what the count is. From this number, you can estimate when the next automatic update of statistics will occur.



How we can do the UPDATE STATISTICS in SQL Server?

If you want to find out if an index has had its indexes updated or not, you can use the command, like this:
DBCC SHOW_STATISTICS (table_name , index_name)
This command will not only tell you when statistics were last updated, but also provide you information on the kind of statistics that has been collected for the index you are examining.

You can update the statistics using this command:
USE
EXEC sp_updatestats

SP_UPDATESTATS refers to work against all user-defined tables in the current database.
For the stored procedures performance you need to recompile them in order to take the new plan in to the memory, so run SP_RECOMPILE for all or required stored procedures to compare the sequence of performance

Monday, February 7, 2011

sys.dm_io_virtual_file_stats

Excellent dmf that shows, for each file that SQL Server uses for the databases, stats on how frequently the file has been used by the database. This is one of the primary dynamic management views I use almost daily (well, not usually when I am on vacation, but even sometimes then!)  The file can be any file used in the database, including the log or full text files.

Parameters:

  • database_id – key of the database, retrieved from sys.databases
  • file_id - key of a file in a database. Can be retrieved from sys.database_files if you are working in the context of a database, or sys.master_files will give you all files in all databases

Columns:

  • database_id, file_id – same as the parameter descriptions
  • sample_ms – the number of milliseconds that have passed since the values for sys.dm_io_virtual_file_stats were reset the only way to reset the values is to restart the server.
  • num_of_reads – number of individual read operations that were issued to the file. Note that this is physical reads, not logical reads. Logical reads would not be registered.
  • num_of_bytes_read – the number of bytes that were read, as opposed to the number of reads. The size of a read is not a constant value that can be calculated by the number of reads.
  • Io_stall_read_ms – total time user processes waited for IO. Note that this number can be much greater than the sample_ms. If 10 processes are trying to use the file simultaneously, but the disk is only able to serve 1, and then you might get 9 seconds waiting over a 10 second time period.
  • num_of_writes , num_of_bytes_written, io_stall_write_ms - the same as the read values, except for writes.
  • io_stall – sum of io_stall_write_ms and io_stall_read_ms
  • size_on_disk_bytes – the size of the file in bytes
  • file_handle – the Windows file handle of the file (Books Online)

Example:

For all databases, get vital stats on how busy the file has been, since the last restart:

SELECT
DB_NAME(mf.database_id)
AS databaseName


,mf.physical_name


,num_of_reads


,num_of_bytes_read


,io_stall_read_ms


,num_of_writes


,num_of_bytes_written


,io_stall_write_ms


,io_stall


,size_on_disk_bytes

FROM
sys.dm_io_virtual_file_stats(NULL,
NULL)
AS divfs

JOIN
sys.master_files
AS mf ON mf.database_id = divfs.database_id


AND mf.file_id
= divfs.file_id

Register an Extended Stored Procedure in SQL Server 2008 R2

Aside from those integrated into SQL Server (e.g. xp_cmdshell) it's not often I come across the need to use Extended Stored Procedures but an application I installed recently required that I add their custom procedure to the SQL Server housing the application data.  Having been years since I had last added one it took a little figuring out since I would've expected to find it in Management Studio under "Server Objects" or "Management" but it's somewhere entirely different. 

To add/register an Extended Stored Procedure you'll need to right-click on…

Databases >> System Databases >> master >> Programmability >> Extended Stored Procedures

…and select "New Extended Stored Procedure".


Next you'll have to provide a (this will be the name the procedure is called by) and the path to the DLL, you can browse to the .dll file by clicking on the ellipsis. 


The next step is vital since at the moment the only people able to actually use the Extended Stored Procedure would by sysadmins.  All you have to do to remedy that is click on "Permissions", click "Search", type "public" in the text box and click OK as follows…


All you have to do now is tick the "Execute" permission to allow the 'public' users of the database (effectively everybody) to be able to use the procedure. 

The completed dialog box should look something like this…


Then click "OK" and all should be well.

How to Enable xp_cmdshell

There's a great little feature of SQL Server that allows you to execute command-line statements, allowing you to run batch files, get directory listings or call executables.  The feature is accessed via the xp_cmdshell Extended Stored Procedure, the syntax is pretty simple and looks like this…

EXEC
xp_cmdshell
'dir *.exe';

In the wrong hands xp_cmdshell could cause havoc, for that reason it is disabled by default and in order to enable it you will need to run the following…

– To allow advanced options to be changed.
EXEC
sp_configure
'show advanced options',
1
GO
– To update the currently configured value for advanced options.
RECONFIGURE
GO
– To enable the feature.
EXEC
sp_configure
'xp_cmdshell',
1
GO
– To update the currently configured value for this feature.
RECONFIGURE
GO

Reset Default Database from Command Line

During a recent consolidation exercise I was migrating one database at a time and then taking the old copy offline, unfortunately one of the DBs had (un-knowingly) been my default database and next time I tried to log in I received the dreaded "Cannot open user default database. Login Failed." Message.

In my case it was to execute the sp_defaultdb stored procedure using the OSQL utility, for example…

osql -Sservername -E -dmaster -Q"sp_defaultdb 'username','master'"