Welcome to my World of SQL

How to get a history of SQL scripts that have been run on a DB

2012-02-11T07:23:00.001-08:00

If you don’t have any sort of auditing mechanism enabled, its really hard to find get these information. But you may get that info from the cached plan. p

first step is to make sure that “optimize for ad hoc workloads” option is disabled on the sql server.

EXEC sp_CONFIGURE 'show advanced options',1
RECONFIGURE
GO
EXEC sp_CONFIGURE 'optimize for ad hoc workloads'
RECONFIGURE
GO

if the configured value is 1, then you may find those result using the following query which uses one of my favorite dmvs

SELECT  dest.text,deqs.last_execution_time
FROM    sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

remember it is not guaranteed to return a results; the dmvs stores the data from the last sql server service start.

Find all SQL Objects with QUOTED_IDENTIFIER set to OFF

2012-02-02T06:54:00.001-08:00

Whenever you create or recreate an SP, make sure QUOTED_IDENTIFIER is set to ON. The default value of OFF is there for backward compatibility only. Many modern features of SQL Server (such as filtered indexes, computed persisted columns, xml columns ) require Quoted Identifiers enabled.

Here’s is the script to list all objects with QUOTED_IDENTIFIER set to OFF

  1: SELECT  SCHEMA_NAME(s.[schema_id]) + '.' + s.name AS name
  2:        ,s.create_date
  3:        ,s.modify_date
  4:        ,OBJECTPROPERTY(s.[object_id], 'ExecIsQuotedIdentOn') AS IsQuotedIdentOn
  5:        ,s.type_desc AS object_type
  6: FROM    sys.objects s
  7: WHERE   s.type IN ( 'P', 'TR', 'V', 'IF', 'FN', 'TF' )
  8:         AND OBJECTPROPERTY(s.[object_id], 'ExecIsQuotedIdentOn') = 0
  9: ORDER BY SCHEMA_NAME(s.[schema_id]) + '.' + s.name DESC 
 10: 
 11:

Jobs failing due to "incorrect settings: QUOTED_IDENTIFIER"

2012-02-02T06:45:00.001-08:00

Did you ever noticed after adding a filtered index or a Computed column, jobs/stored procedures that were using these tables suddenly started failing with the following error

Executed as user: XXXXX\XXXXXX. DELETE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. [SQLSTATE 42000] (Error 1934). The step failed.

The work around of this is to add SET QUOTED_IDENTIFIER ON to each job step.

Indexes and Constraints on a Partitioned Table

2012-01-19T14:19:00.001-08:00

In a partitioned table, the partition column must be a part of:

The clustered index key.
The primary key.
Unique index and uniqueness constraint keys.

The partition column must be part of the clustered index key. This makes sense because one of the purposes of a clustered index is to physically organize the pages of a table, and partitioning affects the physical structure of a table as well. SQL Server will internally enforce that the partition column is part of the clustered key, when the table is placed on the partition scheme and the clustered index is created.

The partition column must also be part of the primary key of the partitioned table, if one is declared, whether the primary key is clustered or no clustered. A primary key has an underlying index to enforce uniqueness. You can place the partitioned column after the original primary key columns.

Any unique index must also have the partition column as part of its key, so that SQL Server can enforce uniqueness across the entire set of partitions. Therefore any uniqueness constraint must also have the partition column as part of its key. If your unique index or constraint cannot contain the partitioned column, you can enforce the uniqueness using a DML trigger.

For secondary indexes that are not unique or clustered, the requirements are relaxed somewhat. Still, the benefits of including the partition column in a secondary index can be significant. When secondary indexes have the partition column as a part of their key, and use the same or equivalent partition scheme, the indexes are partitioned and are said to be aligned with the underlying object (heap or clustered index). SQL Server automatically adds the partition column to a secondary nonunique index as an included column if the CREATE INDEX statement does not already contain the partition column.

A secondary index does not have to use the same partition function as the underlying partitioned table to achieve index alignment, as long as the partition function used by each has the same characteristics (equivalent data types of the partition column, number and values of the boundary values, and range direction.) However, it is much more convenient to use the same partition function and partition scheme for the indexes and the underlying partitioned table.

Index alignment helps in achieving partition elimination, where the query processor can eliminate inapplicable partitions from a query plan to access just the partitions required by the query. Index alignment is also required for using the SWITCH statement, which we’ll cover in the next section. If you have a nonaligned secondary index on a table and need to use the SWITCH option, you can always disable the index during the switch process and re-enable it when done.

More details : http://technet.microsoft.com/en-us/library/dd578580(SQL.100).aspx

What’s new on SQL SERVER 2008 SP3

2012-01-19T08:22:00.001-08:00

Microsoft SQL Server Sustained Engineering group is pleased to announce the release of SQL Server 2008 Service Pack 3. Both the Service Pack and Feature Pack updates are available for download on the Microsoft Download Center. As part of our continued commitment to software excellence for our customers, this upgrade is free and doesn’t require an additional service contract.

SQL Server 2008 SP3 contains fixes to issues that have been reported through our customer feedback platforms and Hotfix solution provided in SQL Server 2008 SP2 Cumulative Update 1 thru to Cumulative Update 4. Service Pack 3 also includes supportability enhancements and issues that have been reported through Windows Error Reporting system.

A few customers requested updates in Microsoft SQL Server 2008 SP3 are:

Enhanced upgrade experience from previous versions of SQL Server to SQL Server 2008 SP3. In addition, we have increased the performance & reliability of the setup experience.
In SQL Server Integration Services logs will now show the total number of rows sent in Data Flows.
Enhanced warning messages when creating the maintenance plan if the Shrink Database option is enabled.
Resolving database issue with transparent data encryption enabled and making it available even if certificate is dropped.
Optimized query outcomes when indexed Spatial Data Type column is referenced by DTA (Database Tuning Advisor).
Superior user experience with Sequence Functions (e.g Row_Numbers()) in a Parallel execution plan.

To obtain SQL Server 2008 SP3 with its improved security and supportability please visit the links below:

SQL Server Execution Context and Security

2011-12-02T07:51:00.001-08:00

What is Execution Context?

Whenever a user connects to SQL Server, it will create the User Session called Execution Content which has the all the user access details and whenever that user executes a SQL statement, SQL Server uses these details to validate the user permissions before executing the query.

Execution context is represented by a pair of security tokens: a login token and a user token. The tokens identify the primary and secondary principals against which permissions are checked and the source used to authenticate the token. A login connecting to an instance of SQL Server has one login token and one or more user tokens, depending on the number of databases to which the account has access.

What is Principals in SQL Server ?

Principals are the individuals, groups, and processes that can request SQL Server resources. Principals are categorized by their scope. Every principal has security identifier

Windows level
SQL Server level
Database level

Windows-level principals

Windows Domain Login
Windows Local Login

SQL Server-level principal

SQL Server Login

Database-level principals

Database User
Database Role
Application Role

What are SQL Server Securable?

SQL Server Securable contains three scopes, which are used to assign permissions to users. The securables are nested and each securable contains various other securables. The securable scopes are as follows:

· Server: It includes server roles, logins, etc.

· Database: It includes database users, application roles, database roles, etc.

· Schema: It includes various database objects such as tables, views, procedures, etc.

The securables are used to assign permissions to the users based on scope and the tasks assigned. The issues related to the connectivity to databases, accessing database objects, etc., can be resolved by granting or denying the permissions to the users.

What is the use of the Public database role in SQL Server?

Every database user belongs to the public database role. When a user has not been granted or denied specific permissions on a securable, the user inherits the permissions granted to public on that securable.

Explain Certificate based SQL Server Logins/Principals?

Server principals with names enclosed by double hash marks (##) are for internal system use only. The following principals are created from certificates when SQL Server is installed, and should not be deleted.

##MS_SQLResourceSigningCertificate##
##MS_SQLReplicationSigningCertificate##
##MS_SQLAuthenticatorCertificate##
##MS_AgentSigningCertificate##
##MS_PolicyEventProcessingLogin##
##MS_PolicySigningCertificate##
##MS_PolicyTsqlExecutionLogin##

Moving tempdb to a new location

2011-11-07T06:50:00.001-08:00

The following example moves tempdb from its current location on the disk to another disk location. Because tempdb is re-created each time the MSSQLSERVER service is started, you do not have to physically move the data and log files. The files are created when the service is restarted in step 3. Until the service is restarted, tempdb continues to function in its existing location.

Determine the logical file names of the tempdb database and their current location on disk.

  1: SELECT name, physical_name
  2: FROM sys.master_files
  3: WHERE database_id = DB_ID('tempdb');
  4: GO
  5:

2. Change the location of each file by using ALTER DATABASE.

  1: USE master;
  2: GO
  3: ALTER DATABASE tempdb 
  4: MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
  5: GO
  6: ALTER DATABASE  tempdb 
  7: MODIFY FILE (NAME = templog, FILENAME = 'E:\SQLData\templog.ldf');
  8: GO
  9:

3. Stop and restart the instance of SQL Server.

4. Verify the file change.

  1: SELECT name, physical_name
  2: FROM sys.master_files
  3: WHERE database_id = DB_ID('tempdb');

5. Delete the tempdb.mdf and templog.ldf files from their original location

SQL Server Service Accounts

2011-11-01T07:50:00.001-07:00

Service account for	Files and folders	Access
MSSQLServer	Instid\MSSQL\backup D:\backups	Full control
	Instid\MSSQL\binn	Read, Execute
	Instid\MSSQL\data + G:\MSSQL10.MSSQLSERVER\MSSQL\DATA + index folders H:\MSSQL10.MSSQLSERVER\MSSQL\LOG\ (include Tempdb directory)	Full control
	Instid\MSSQL\FTData	Full control
	Instid\MSSQL\Install	Read, Execute
	Instid\MSSQL\Log	Full control
	Instid\MSSQL\Repldata	Full control
	100\shared	Read, Execute
SQLServerAgent	Instid\MSSQL\binn	Full control
	Instid\MSSQL\Log	Read, Write, Delete, Execute
	100\com	Read, Execute
	100\shared	Read, Execute
	100\shared\Errordumps	Read, Write
	ServerName\EventLog	Full control
FTS	Instid\MSSQL\FTData	Full control
	Instid\MSSQL\FTRef	Read, Execute
	100\shared	Read, Execute
	100\shared\Errordumps	Read, Write
	Instid\MSSQL\Install	Read, Execute
	Instid\MSSQL\jobs	Read, Write
SQLServerReportServerUser	Instid\Reporting Services\Log Files	Read, Write, Delete
	Instid\Reporting Services\ReportServer	Read, Execute
	Instid\Reportingservices\Reportserver\global.asax	Full control
	Instid\Reportingservices\Reportserver\Reportserver.config	Read
	Instid\Reporting Services\reportManager	Read, Execute
	Instid\Reporting Services\RSTempfiles	Read, Write, Execute, Delete
	100\shared	Read, Execute
	100\shared\Errordumps	Read, Write
SQL Server Browser	100\shared\ASConfig	Read
	100\shared	Read, Execute
	100\shared\Errordumps	Read, Write
SQLWriter	N/A (Runs as local system)
User	Instid\MSSQL\binn	Read, Execute
	Instid\Reporting Services\ReportServer	Read, Execute, List Folder Contents
	Instid\Reportingservices\Reportserver\global.asax	Read
	Instid\Reporting Services\ReportManager	Read, Execute
	Instid\Reporting Services\ReportManager\pages	Read
	Instid\Reporting Services\ReportManager\Styles	Read
	100\tools	Read, Execute
	90\tools	Read, Execute
	80\tools	Read, Execute
	100\sdk	Read
	Microsoft SQL Server\100\Setup Bootstrap	Read, Execute
	+any physical folders the application access

** this needs the service restart

1. Need separate domain accounts with least priority for each service. Create one each for the following services

a. SQL Server

b. SQL Server agent

c. Reporting services

2. To change the service accounts, password, service startup type, or other properties of any SQL Server–related service, use SQL Server Configuration Manager. For reporting services, use the Reporting Services Configuration Tool.

** must grant SQL Service Account account the windows permission "Perform Volume maintenance Tasks"

What is Database Mirroring

2011-11-01T07:47:00.001-07:00

In the proposed database mirroring solution there will be two groups of active-passive windows clusters deployed at each location (PRIMARY SITE and Secondary Site). The Primary Site will be the primary site and the Secondary Site will be the disaster recovery (DR) site.
The different failure scenarios are as follows:
- In case of a failure at the Primary Site, Node-A would automatically fail-over to Node-B on the Primary Site.
-In case of a Node-B failure, Node-C at Disaster Recovery site will need to be manually, brought online.
-In case of a Node-C failure, Node-C will automatically fail-over to Node-D.
Asynchronous database mirroring is to be implemented to replicate the data across these sites. Fail-over and fail-back is to be performed manually from PRIMARY SITE to Secondary Site.
Database Mirroring Overview
Database mirroring is a software technology built into Microsoft SQL Server providing high availability of application databases. Mirroring is implemented on a per database basis and works only with databases that use the full recovery model. The simple and bulk-logged recovery models do not support database mirroring.
Database mirroring offers a substantive increase in availability over the level previously possible using Microsoft SQL Server and provides an easy-to-manage alternative or supplement to failover clustering or log shipping. Database mirroring works by maintaining a hot standby server. During a typical mirroring session, after a production server fails, client applications can recover quickly by reconnecting to the standby server.
Pros
- Database mirroring architecture is more robust and efficient than Database Log Shipping. It can be configured to replicate the changes synchronously to minimize data loss.
-Database mirroring has automatic server failover and client failover capabilities.
-Configuration is simpler than log shipping and replication, and has built-in network encryption support (AES algorithm).
-Because propagation can be done asynchronously it requires less bandwidth than synchronous method (e.g. host-based replication, clustering). Replication is not limited by geographical distance with current technology.
-Database mirroring supports full-text catalogs.
-Database mirroring does not require special hardware (such as shared storage, heart-beat connection) and cluster hardware, thus potentially has lower infrastructure cost.
Cons
-As the mirroring database is in a constantly restoring state a database snapshot is required to use the database for reporting.
-There is a risk in using automatic fail-over on a SQL Server cluster set-up, as in case of hardware failure on primary node there is a possibility of fail-over occurring to the mirrored database instead of secondary node.
-Additional SQL Server instances may be required for witnessing.

How do you specify a different port number in SQL Management Studio?

2011-10-11T08:00:00.001-07:00

Specifying a port number on SSMS is pretty easy

Under the server name , specify the server(or ip)\instanceName, ( comma) port

eg: 10.10.10.151\SQL2008,1433

Another way is to setup an alias in Config Manager. Then simply type that alias name when you want to connect. This makes it much easier and is more prefereable when you have to manage several servers/instances and/or servers on multiple ports and/or multiple protocols. Give them friendly names and it becomes much easier to remember them.

Finding the Port Number for a particular SQL Server Instance

2011-10-11T07:48:00.001-07:00

One of the developers recently asked me this question: “I have a SQL Server instance running – how can I tell what port it is running on?”. There are a couple of ways of finding this information.

1) Using the GUI:

If you expand the SQL Server Network Configuration and select Protocols for MSSQLSERVER (default instance) in the right hand pane you’ll see your protocols (Shared Memory, Named Pipes, TCP/IP, VIA). If you double click on TCP/IP the properties dialog box will appear and by selecting the IP Addresses tab you will now see your IP addresses and which TCP port they are running under.

2) Check the error log. You will see an entry like: “Server is listening on [ 'any' <ipv4> 1433].” The last 4 numbers denote the TCP/IP port number that is being used by that particular instance of SQL Server.

3) Registry entry: HKLM\Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\TCP

and you will see TCPPort as one of the entries and it’s value represents the port number for that instance. In case you are using a named instance, then the registry entry will be: HKLM\Software\Microsoft\Microsoft SQL Server\<name of the instance>\MSSQLServer\SuperSocketNetLib\TCP

4) You can also chose to trace the client to server communication by using Microsoft Network Monitor or a network sniffer utility. You can also use the TCP/IP netstat utility.

Task Manager
View | Select Columns | Check PID | OK
Find sqlservr.exe as the Image Name, note the PID
Command prompt:
netstat -ano | findstr *PID*

The PID is displayed in the far right. Look for the TCP entry. The port will be after the colon (e.g. 0.0.0.0:1433 means the port is 1433).”

Table Size in SQL Server | Find Rows and Disk space Usage

2011-08-31T15:18:00.001-07:00

There are two ways to find out the table size;

first option will be to run the system stored procedure “sp_spaceUsed”

  1: EXEC sp_SpaceUsed 'table_name'

Option 2 uses the dmv sys.dm_db_partition_stats

  1: SELECT  t.schema_name + '.' + t.table_name AS table_name
  2:        ,t.index_name
  3:        ,SUM(t.used) AS used_in_kb
  4:        ,SUM(t.reserved) AS reserved_in_kb
  5:        ,SUM(t.tbl_rows) AS rows
  6: FROM    ( SELECT    s.Name schema_name
  7:                    ,o.Name table_name
  8:                    ,COALESCE(i.Name, 'HEAP') index_name
  9:                    ,p.used_page_count * 8 used
 10:                    ,p.reserved_page_count * 8 reserved
 11:                    ,p.row_count ind_rows
 12:                    ,CASE WHEN i.index_id IN ( 0, 1 ) THEN p.row_count
 13:                          ELSE 0
 14:                     END tbl_rows
 15:           FROM      sys.dm_db_partition_stats p
 16:           INNER JOIN sys.objects AS o ON o.object_id = p.object_id
 17:           INNER JOIN sys.schemas AS s ON s.schema_id = o.schema_id
 18:           LEFT OUTER JOIN sys.indexes AS i ON i.object_id = p.object_id
 19:                                               AND i.index_id = p.index_id
 20:           WHERE     o.type_desc = 'USER_TABLE'
 21:                     AND o.is_ms_shipped = 0 ) AS t
 22: GROUP BY t.schema_name
 23:        ,t.table_name
 24:        ,t.index_name
 25: ORDER BY 5 DESC
 26: 
 27:

Generating Calendar in SQL Server 2008

2011-08-05T07:44:00.001-07:00

Here is the simple query that generates a calendar.

  1: DECLARE @startDay DATE = '20110101'
  2:        ,@endDay DATE = '20130101'
  3: 
  4: ;WITH calendar ( [Date], [Year], [Quarter], [Month], [Day], [DayOfWeek], [MonthName], [DayName], [WeekNumber], isWeekDay )
  5: AS ( SELECT @startDay
  6:            ,YEAR(@startDay)
  7:            ,DATEPART(qq, @startDay)
  8:            ,DATEPART(mm, @startDay)
  9:            ,DATEPART(dd, @startDay)
 10:            ,DATEPART(dw, @startDay)
 11:            ,DATENAME(month, @startDay)
 12:            ,DATENAME(dw, @startDay)
 13:            ,DATEPART(wk, @startDay)
 14:            ,CASE WHEN DATEPART(dw, @startDay) IN ( 1, 7 ) THEN 0 ELSE 1 END
 15:    UNION ALL
 16:    SELECT  DATEADD(dd, 1,[date])
 17:            ,YEAR(DATEADD(dd, 1,[date]))
 18:            ,DATEPART(qq, DATEADD(dd, 1,[date]))
 19:            ,DATEPART(mm, DATEADD(dd, 1,[date]))
 20:            ,DATEPART(dd, DATEADD(dd, 1,[date]))
 21:            ,DATEPART(dw, DATEADD(dd, 1,[date]))
 22:            ,DATENAME(month, DATEADD(dd, 1,[date]))
 23:            ,DATENAME(dw, DATEADD(dd, 1,[date]))
 24:            ,DATEPART(wk, DATEADD(dd, 1,[date]))
 25:            ,CASE WHEN DATEPART(dw, DATEADD(dd, 1,[date])) IN ( 1, 7 ) THEN 0 ELSE 1 END
 26:    FROM     calendar
 27:    WHERE    DATEADD(dd, 1,[date]) < @endDay )
 28: SELECT *
 29: FROM   calendar
 30: OPTION  ( MAXRECURSION 1000 )
 31: 
 32:

Is TRUNCATE TABLE transactional ?

2011-07-29T13:45:00.001-07:00

1: CREATE TABLE tTest ( i INT )  
2: INSERT tTest ( i ) VALUES( 1 ) 
3: INSERT tTest ( i ) VALUES( 1 ) 
4: INSERT tTest ( i ) VALUES( 1 ) 
5: INSERT tTest ( i ) VALUES( 1 ) 
6: 
7: BEGIN TRAN 
8:  TRUNCATE TABLE tTest 
9:  SELECT * FROM tTest 
10: ROLLBACK 
11: 
12: SELECT COUNT(*) FROM tTest 
13: DROP TABLE tTest 
14: 
15:

So, yes - it is subject to the transaction; also - it is a logged operation, its just that the individual rows are not logged "individually" like they would if you used DELETE.

How to remove blank line in SSMS “Find and Replace” dialogue

2011-07-29T13:41:00.001-07:00

The answer is simple; in Management Studio go into Find and Replace (ctrl+H ) , in "Find Options" check "Use" and select "Regular Expressions".

Now, in the Find what: box enter the text ^\n

In the Replace with: box enter nothing, please don't take me literally on that and actually type the word nothing, I mean leave the text box empty.

Click Replace All.

How to find relationship between tables

2011-07-20T08:03:00.001-07:00

As a DBA or even as a SQL Server Programmer, we may need a quick script to find relationship between tables. Here is the script which helped me a lot, hope this will be useful for you too

  1: SELECT  OBJECT_NAME(rkeyid) ParentTable
  2:        ,OBJECT_NAME(fkeyid) ChildTable
  3:        ,OBJECT_NAME(constid) FKey
  4:        ,c1.name FKey_Col
  5:        ,c2.name Ref_KeyCol
  6: FROM    sys.sysforeignkeys s
  7: INNER JOIN sys.syscolumns c1 ON ( s.fkeyid = c1.id
  8:                                   AND s.fkey = c1.colid
  9:                                 )
 10: INNER JOIN syscolumns c2 ON ( s.rkeyid = c2.id
 11:                               AND s.rkey = c2.colid
 12:                             )
 13: ORDER BY 1,2

TSQL Function to convert decimal to Hex, Octal or any other base

2011-07-20T07:58:00.001-07:00

Frequently I see the questions in newsgroups about a function to convert integer value to other bases like base 2 (binary), base 8 (octal) and base 16(hex). Following TSQL function, which was orginally mentioned by Itzik Ben-Gan in his book Inside Microsoft SQL Server 2005:TSQL Querying, provides you the ability to convert a given integer into any target base. I have just updated the function with more meaningful names and added some comments to clear the logic.

USE [AdventureWorks]
GO
IF EXISTS
(
    SELECT * FROM sys.objects
    WHERE object_id = OBJECT_ID(N'[dbo].[fn_decToBase]')
    AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')
)
DROP FUNCTION [dbo].[fn_decToBase]
Go
Create function [dbo].[fn_decToBase]
(
    @val as BigInt,
    @base as int
)
returns varchar(63)
as
Begin
    /* Check if we get the valid base */
    If (@val<0) OR (@base < 2) OR (@base> 36) Return Null;
    /* variable to hold final answer */
    Declare @answer as varchar(63);
    /*    Following variable contains all
        possible alpha numeric letters for any base
    */
    Declare @alldigits as varchar(36);
    Set @alldigits='0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
    /*    Set the initial value of
        final answer as empty string
    */
    Set @answer='';
    /* Loop until your source value is greater than 0 */
    While @val>0
    Begin
        Set @answer=Substring(@alldigits,@val % @base + 1,1) + @answer;
        Set @val = @val / @base;
    End
    /* Return the final answer */
    return @answer;
End

TSQL Function to encode HTML Text

2011-07-20T07:55:00.001-07:00

While browsing through the SharePoint content database, I found a very useful TSQL utility function which can be used in any application. This function encodes the given html so that it can be safely used in XML tags and other html rendering requirements.

Please note, this function is originally provided in Sharepoint content database and I have just copied it because of its usefulness. PLEASE DO NOT CALL it directly from Sharepoint database as it is not recommended and supported by Microsoft. Just create this function in your own database and modify it as per your requirement.

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
go
CREATE FUNCTION [dbo].[fn_HtmlEncode] (
       @Value NVARCHAR(1023)
      ,@PreserveNewLine BIT )
RETURNS NVARCHAR(4000)
AS 
	BEGIN
	DECLARE @Result NVARCHAR(4000)
	SELECT  @Result = @Value
	IF @Result IS NOT NULL AND LEN(@Result) > 0 
	BEGIN
		 SELECT @Result = REPLACE(@Result, N'&', N'&amp;')
		 SELECT @Result = REPLACE(@Result, N'<', N'<')
		 SELECT @Result = REPLACE(@Result, N'>', N'>')
		 SELECT @Result = REPLACE(@Result, N'''', N'&#39;')
		 SELECT @Result = REPLACE(@Result, N'"', N'&quot;')
		 IF @PreserveNewLine = 1 
			SELECT  @Result = REPLACE(@Result, CHAR(10), CHAR(10) + N'<br>')
	END
    RETURN @Result
END

SQL Server Migration Checklist

2011-07-19T18:27:00.001-07:00

Here is a quick checklist for your SQL Server Migration.

1. Build your New Server, Install SQL Server and required updates and keep the server ready for migration

2. Stop Application Service(s) and kill all the connections from sql server.
This is to ensure that the no applications are connected to the Databases during the migration process.

3. Disable all the jobs that refers to this database

4. Take the database to ‘SINGLE_USER’mode

5. Take a FULL backup of all the User databases that are involved in the Migration Process.

6. Move the backups to the destination server or a Shared location, then restore them to the appropriate drives on the destination.

7. Change the compatibility level of the databases (Optional)
Do this if the applications connecting to these databases are independent of the database compatibility level.

8. Transfer logins using SSIS (Transfer Logins Task) or using "sp_help_revlogin"
More information about sp_help_revlogin is at http://support.microsoft.com/kb/246133

9. Check for Orphaned Users in the databases and Fix them (if Any)

10. Update Usage on the migrated Databases

11. Update Stats on the migrated Databases

12. Re-Index or Re-Organize Indexes on the migrated Databases

13. Transfer Jobs using SSIS or manually create them

14. Build Maintenance plans (if Any)

15. Recompile database objects if required

16. Move or rebuild SSIS or DTS packages (if Any)

17. Create Alerts and Operators (if Any)

18. Setup High Availability Options (if Any Like Replication, LogShipping, Mirroring)

19. Test the High Availability options that were setup in the previous step

20. Point the Application(s) to new Server and start the Application Service(s)

21. If the application uses any of the user defined error messages, you may need to copy them to the new server.

22. Test the Application(s)

DBCC REINDEX vs DBCC INDEXDEFRAG

2011-06-23T07:34:00.001-07:00

Note that DBCC DBREINDEX uses one big transaction, killing the operation in the middle would rollback the entire transaction. It would not help you reduce log space consumption if you restart the index rebuild after killing.

I noticed that you were doing index rebuild monthly. DBCC DBREINDEX is an offline operation. While doing clustered index rebuild, the entire table is offline. You should only consider rebuilding index if fragmentation affects your query performance.

If after a while, your query performance goes down because of fragmentation, do you consider using DBCC INDEXDEFRAG to reduce fragmentation and improve space usage?

Three biggest advantages of DBCC INDEXDEFRAG are

(1). If fragmentation is low (under 20%), you will use less log space than DBCC DBREINDEX;

(2). DBCC INDEXDEFRAG is an online operation and uses in-place page reorganization algorithm, hence space requirement for data is almost none and other query could access the data while defrag is going on;

(3). You can kill and restart in the middle of defrag and all finished work is preserved.

The major drawbacks if DBCC INDEXDEFRAG are

(1). It is slower than DBCC DBREINDEX if fragmentation is high;

(2). Index statistics are not updated after DBCC INDEXDEFRAG

Lists user login attempts to a SQL Server (failed and successful login attempts)

2011-06-14T07:47:00.001-07:00

This script is useful for security audits (helping you gather information about failed login attempts), and for checking recent activity by a particular login (before you delete a login, for example).
The script lists all failed login attempts by a user, including the IP address of the computer from which the login attempts were made. The last successful login for a user is also listed.
Note that security logging must be enabled on the SQL Server you are monitoring, otherwise this script will not return accurate results.

DECLARE @TSQL  NVARCHAR(2000)
DECLARE @lC    INT
CREATE TABLE #TempLog (
      LogDate     DATETIME,
      ProcessInfo NVARCHAR(50),
      [Text] NVARCHAR(MAX))
CREATE TABLE #logF (
      ArchiveNumber     INT,
      LogDate           DATETIME,
      LogSize           INT
)
INSERT INTO #logF   
EXEC sp_enumerrorlogs
SELECT @lC = MIN(ArchiveNumber) FROM #logF
WHILE @lC IS NOT NULL
BEGIN
      INSERT INTO #TempLog
      EXEC sp_readerrorlog @lC
      SELECT @lC = MIN(ArchiveNumber) FROM #logF 
      WHERE ArchiveNumber > @lC
END
--Failed login counts. Useful for security audits.
SELECT 'Failed - ' + CONVERT(nvarchar(5), COUNT(Text)) + ' attempts' AS [Login Attempt], Text AS Details
FROM #TempLog 
where ProcessInfo = 'Logon' 
and Text like '%failed%'
Group by Text
--Find Last Successful login. Useful to know before deleting "obsolete" accounts.
SELECT Distinct 'Successful - Last login at (' + CONVERT(nvarchar(64), MAX(LogDate)) + ')' AS [Login Attempt], Text AS Details
FROM #TempLog 
where ProcessInfo = 'Logon' and Text like '%succeeded%'
and Text not like '%NT AUTHORITY%'
Group by Text
DROP TABLE #TempLog
DROP TABLE #logF

Script to Generate BCP out and Bulk insert statements

2011-06-14T07:43:00.001-07:00

This will be useful while migrating the data from one server to another

SET NOCOUNT ON
GO
SELECT @@SERVERNAME
DECLARE @path nvarchar(2000), @batchsize nvarchar(40), 
        @format nvarchar(40), @serverinstance nvarchar(200), 
        @security nvarchar(800)
 
SET @path = 'C:\Temp\';
SET @batchsize = '100000' -- COMMIT EVERY n RECORDS
SET @serverinstance = @@SERVERNAME --SQL Server \ Instance name
SET @security = ' -T ' -- -T (trusted), -Uloginid -Ploginpassword
 
--GENERATE CONSTRAINT NO CHECK
PRINT '--NO CHECK CONSTRAINTS'
SELECT 'ALTER TABLE ' + QUOTENAME( TABLE_NAME ) + ' NOCHECK CONSTRAINT ' 
+  QUOTENAME( CONSTRAINT_NAME )
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
 
--DISABLE TRIGGERS
PRINT '--DISABLE TRIGGERS'
SELECT 'ALTER TABLE ' + QUOTENAME( TABLE_NAME ) + ' DISABLE TRIGGER ALL'
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
 
--BCP-OUT TABLES
PRINT '--BCP OUT TABLES '
SELECT  'bcp "' + QUOTENAME( TABLE_CATALOG ) + '.' + QUOTENAME( TABLE_SCHEMA ) 
+ '.' + QUOTENAME( TABLE_NAME ) + '" out "' + @path + '' + TABLE_NAME + '.dat" -q -b"' 
+ @batchsize + '" -e"' + @path + '' + TABLE_NAME + '.err" -n -CRAW -o"' + @path + '' 
+ TABLE_NAME + '.out"  -S"' + @serverinstance + '" ' + @security + ''
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
 
-- CREATE NON-XML FORMAT FILE
PRINT '--NON-XML FORMAT FILE'
SELECT  'bcp "' + QUOTENAME( TABLE_CATALOG ) + '.' + QUOTENAME( TABLE_SCHEMA ) + '.' 
+ QUOTENAME( TABLE_NAME ) + '" format nul -n -CRAW -f "' + @path + '' 
+ TABLE_NAME + '.fmt"  --S"' + @serverinstance + '" ' + @security + ''
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
 
-- CREATE XML FORMAT FILE
PRINT '--XML FORMAT FILE'
SELECT  'bcp "' +QUOTENAME( TABLE_CATALOG ) + '.' + QUOTENAME( TABLE_SCHEMA ) 
+ '.' + QUOTENAME( TABLE_NAME ) + '" format nul -x -n -CRAW -f "' 
+ @path + '' + TABLE_NAME + '.xml"  -S"' + @serverinstance + '" ' + @security + ''
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
 
--TRUNCATE TABLE
PRINT '--TRUNCATE TABLE'
SELECT 'TRUNCATE TABLE ' +QUOTENAME( TABLE_NAME ) + ' 
GO '
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
 
--BULK INSERT
PRINT '--BULK INSERT'
SELECT DISTINCT 'BULK INSERT ' + QUOTENAME(TABLE_CATALOG) + '.' 
+ QUOTENAME( TABLE_SCHEMA ) + '.' + QUOTENAME( TABLE_NAME ) + ' 
   FROM ''' + @path + '' + TABLE_NAME + '.Dat'' 
   WITH (FORMATFILE = ''' + @path + '' + TABLE_NAME + '.FMT'',
         BATCHSIZE = ' + @batchsize + ',
         ERRORFILE = ''' + @path + 'BI_' + TABLE_NAME + '.ERR'', 
         TABLOCK);
GO ' 
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' 
 
--OPENROWSET
PRINT '--OPENROWSET'
SELECT DISTINCT 'INSERT INTO ' + QUOTENAME(TABLE_CATALOG) + '.' 
+ QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + ' 
    SELECT *
      FROM  OPENROWSET(BULK  ''' + @path + '' + TABLE_NAME + '.Dat'',
      FORMATFILE=''' + @path + '' + TABLE_NAME + '.Xml''
      ) as t1 ;
GO ' 
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
 
--GENERATE CONSTRAINT CHECK CONSTRAINT TO VERIFY DATA AFTER LOAD
PRINT '--CHECK CONSTRAINT'
SELECT 'ALTER TABLE ' + QUOTENAME( TABLE_NAME ) + ' CHECK CONSTRAINT ' 
+  QUOTENAME( CONSTRAINT_NAME ) 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
 
--ENABLE TRIGGERS
PRINT '--ENABLE TRIGGERS'
SELECT 'ALTER TABLE ' + QUOTENAME( TABLE_NAME ) + ' ENABLE TRIGGER ALL'
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

Query to return fragmentation information on partitioned indexes

2011-06-01T07:17:00.001-07:00

This will very useful, if you have implemented the partitioning functionality .

SELECT  OBJECT_NAME(a.object_id) AS object_name
       ,a.index_id
       ,b.name
       ,b.type_desc
       ,a.partition_number
       ,a.avg_fragmentation_in_percent
FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') a
JOIN    sys.indexes b ON a.object_id = b.object_id AND a.index_id = b.index_id
WHERE   a.object_id = OBJECT_ID('tableName')-- need to change the tablename here
ORDER BY OBJECT_NAME(a.object_id)
       ,a.index_id
       ,b.name
       ,b.type_desc
       ,a.partition_number

once the fragmentation has been identified use the following script to rebuild the index on a single partition. in this example I am rebuilding the partition 18

ALTER INDEX PK_tableName
ON dbo.tableName
REBUILD PARTITION = 18 ;
GO

Minimally Logged Inserts in sql server 2008

2011-05-29T20:48:00.001-07:00

This is another great feature that was introduced in sql server 2008. This is one of the enhanced features of ‘SELECT INTO ‘ statements. All you need to do is to make sure that the following conditions are met

ensure that the database recovery model is bulk-logged / simple
enable the Trace flag 610.
insert the data ordered by the clustered index

So what is exactly happening behind the scenes ? Initial inserts may be fully logged if the data pages they are filling already contain data, but however any new data pages added to the table will be minimally logged as long as the above 3 criteria are satisfied.

Here is an example

        1: DBCC TRACEON (610)

       2: INSERT INTO TargetTable

       3: SELECT * 

       4: FROM sourceTable

       5: ORDER BY <ClusteredIndex>

       6: DBCC TRACEOFF (610)

Parameter Sniffing

2011-05-23T10:02:00.001-07:00

When a stored procedure is first executed SQL Server looks at the input parameters and uses this as guidance to build the query plan. This is known as "parameter sniffing". This is good as long as the input parameters for the first invocation are typical for future invocations. But if that is not the case this will cause performance problems.

Okay, I understand parameter sniffing, How should I handle it ?

There are different approaches to handle parameter sniffing. but we need an example to work with.

CREATE PROCEDURE dbo.getShippingInfo
@OrderID int
AS
BEGIN  
	SET NOCOUNT ON    
	SELECT CustomerName, Address, zipCode, province, country    
	FROM dbo.Orders    
	WHERE OrderID = @OrderID 
END

1. Replace parameters with local variables

This solution is based on assigning the stored procedure parameters to local variables and then using the local variables in the query. This works because SQL Server is not sniffing local variables and using the local variables in place of parameters forces plan generated based on statistics (in effect this disables parameter sniffing).

       1: CREATE PROCEDURE dbo.getShippingInfo

       2: @OrderID int

       3: AS

       4: BEGIN  

       5:     SET NOCOUNT ON

       6:     DECLARE @spOrderID int =  @OrderID   

       7:     SELECT CustomerName, Address, zipCode, province, country    

       8:     FROM dbo.Orders    

       9:     WHERE OrderID = @spOrderID 

      10: END

2.Execute using WITH RECOMPILE

This solution forces recompile of the stored procedure on each run, that way forcing a fresh query plan for the current parameters. Note that this will recompile all statements inside the stored procedure.

       1: exec dbo.getShippingInfo @orderID = 12345 WITH RECOMPILE

3.Query hint RECOMPILE

SQL Server 2005 offers the new query hint RECOMPILE which will force recompilation of the individual query. This method is better than the prior method because recompilation will affect only one statement and all other queries in the stored procedure will not be recompiled.

       1: CREATE PROCEDURE dbo.getShippingInfo

       2: @OrderID int

       3: AS

       4: BEGIN  

       5:     SET NOCOUNT ON

       6:     SELECT CustomerName, Address, zipCode, province, country    

       7:     FROM dbo.Orders    

       8:     WHERE OrderID = @OrderID 

       9:     OPTION (RECOMPILE)

      10: END

4.Query hint OPTIMIZE FOR

Another new query hint in SQL Server 2005 is OPTIMIZE FOR. It allows specifying a constant that will be used to optimize the query plan instead of the variable. This could be useful if it is known that particular selective value is frequently used to invoke the stored procedure. However, any other parameter value will suffer the same performance problems.

       1: CREATE PROCEDURE dbo.getShippingInfo

       2: @OrderID int

       3: AS

       4: BEGIN  

       5:     SET NOCOUNT ON

       6:     SELECT CustomerName, Address, zipCode, province, country    

       7:     FROM dbo.Orders    

       8:     WHERE OrderID = @OrderID 

       9:     OPTION (OPTIMIZE FOR UNKNOWN)

      10: END

      11:

Plan Guides

Plan guides in SQL Server 2005 provide the opportunity to optimize a query without changing the actual code of the query. This is especially useful when dealing with third party vendor applications where access to code may not be available. A plan guide allows associating query hints with a query without changing the query.

       1: EXEC sp_create_plan_guide      

       2:     @name = N'SolveParameterSniffing',     

       3:     @stmt = N'SELECT CustomerName, Address, zipCode, province, country FROM dbo.Orders WHERE OrderID = @OrderID',     

       4:     @type = N'OBJECT',     

       5:     @module_or_batch = N'getShippingInfo',     

       6:     @params = NULL,     

       7:     @hints = N'OPTION (RECOMPILE)';

USE PLAN query hint

Another plan stability feature in SQL Server 2005 is the USE PLAN "xml_plan" query hint, which allows forcing the use of a specific plan every time the query is run.

find the tables having composite clustered index

2011-05-05T07:25:00.001-07:00

script to find the tables having composite clustered index

;WITH cte AS (
SELECT   OBJECT_NAME(i.object_id) NAME, type_desc, i.index_id ,c.name columnName ,p.rows 
FROM sys.indexes i 
INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns c on ic.column_id = c.column_id AND c.object_id = i.object_id
WHERE i.type = 1 
AND OBJECT_NAME(i.object_id) NOT LIKE  'sys%'
),
cte2 AS (
SELECT *, rn = ROW_NUMBER()OVER ( PARTITION BY NAME ORDER BY columnName desc )
FROM cte 
)
SELECT DISTINCT name, rows 
FROM cte2 b 
WHERE EXISTS (SELECT 1 FROM cte2 a WHERE a.name = b.name AND a.rn >=2 )
ORDER BY 2

Historic information – sql backups

2011-05-04T08:58:00.001-07:00

Need to find out the historic information of backups , try this script

SELECT  
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
   msdb.dbo.backupset.database_name,  
   msdb.dbo.backupset.backup_start_date,  
   msdb.dbo.backupset.backup_finish_date, 
   msdb.dbo.backupset.expiration_date, 
   CASE msdb..backupset.type  
       WHEN 'D' THEN 'Database'  
       WHEN 'L' THEN 'Log'  
   END AS backup_type,  
   msdb.dbo.backupset.backup_size,  
   msdb.dbo.backupmediafamily.logical_device_name,  
   msdb.dbo.backupmediafamily.physical_device_name,   
   msdb.dbo.backupset.name AS backupset_name, 
   msdb.dbo.backupset.description 
FROM   msdb.dbo.backupmediafamily  
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  
WHERE  (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)  
ORDER BY  
   msdb.dbo.backupset.database_name, 
   msdb.dbo.backupset.backup_finish_date

Suggested Max Memory Settings for SQL Server 2005/2008

2011-04-27T07:24:00.001-07:00

It is important to make sure you set the Max server memory setting for SQL Server 2005/2008 to something besides the default setting (which allows SQL Server to use as much memory as it wants, subject to signals from the host OS that it is under memory pressure). This is especially important with larger, busier systems that may be under memory pressure. This setting controls how much memory can be used by the SQL Server Buffer Pool. If you don’t set an upper limit for this value, other parts of SQL Server, and the operating system can be starved for memory, which can cause instability and performance problems.

This is for x64, on a dedicated DB server.

Physical RAM (GB)	MaxMem Setting
2	1500
4	3200
6	4800
8	6700
12	10600
16	14500
24	22400
32	30000
48	45000
64	59000

You can set this value with Transact-SQL like this (it may take 1-3 minutes to complete)

-- Turn on advanced options
EXEC sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
-- See what the current value is for 'max server memory (MB)'
EXEC sp_configure
-- Set max memory = 45000MB 
EXEC sp_configure 'max server memory (MB)', 45000
GO
RECONFIGURE
GO

Use sys.dm_os_performance_counters to get your Buffer cache hit ratio and Page life expectancy counters

2011-04-25T20:52:00.000-07:00

Buffer cache hit ratio

This means what is the percentage that SQL Server had the data in cache and did not have to read the data from disk. Ideally you want this number to be as close to 100 as possible.

In order to calculate the Buffer cache hit ratio we need to query the sys.dm_os_performance_counters dynamic management view. There are 2 counters we need in order to do our calculation, one counter is Buffer cache hit ratio and the other counter is Buffer cache hit ratio base. We divide Buffer cache hit ratio base by Buffer cache hit ratio and it will give us the Buffer cache hit ratio.
Here is the query that will do that, this query will only work on SQL Server 2005 and up.

        1: 1.SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 AS BufferCacheHitRatio

       2: 2.FROM sys.dm_os_performance_counters  a

       3: 3.JOIN  (SELECT cntr_value,OBJECT_NAME 

       4: 4.    FROM sys.dm_os_performance_counters  

       5: 5.    WHERE counter_name = 'Buffer cache hit ratio base'

       6: 6.        AND OBJECT_NAME = 'SQLServer:Buffer Manager') b ON  a.OBJECT_NAME = b.OBJECT_NAME

       7: 7.WHERE a.counter_name = 'Buffer cache hit ratio'

       8: 8.AND a.OBJECT_NAME = 'SQLServer:Buffer Manager'

Page life expectancy

Now let's look at Page life expectancy.

Page life expectancy is the number of seconds a page will stay in the buffer pool, ideally it should be above 300 seconds. If it is less than 300 seconds this could indicate memory pressure, a cache flush or missing indexes.

Here is how to get the Page life expectancy

       1: 1.SELECT *

       2: 2.FROM sys.dm_os_performance_counters  

       3: 3.WHERE counter_name = 'Page life expectancy'

       4: 4.AND OBJECT_NAME = 'SQLServer:Buffer Manager'

script – percentage of identity values being used

2011-04-20T07:34:00.001-07:00

Here is a script which list out all the identity columns and percentage used

SET NOCOUNT ON
SELECT	SCHEMA_NAME(t.schema_id) + '.' +  t.name AS TableName, 
idc.name AS ColumnName,
CASE idc.system_type_id
	WHEN 127 THEN 'bigint'
	WHEN 56 THEN 'int'
	WHEN 52 THEN 'smallint'
	WHEN 48 THEN 'tinyint'
END AS 'DataType',
idc.last_value AS CurrentIdentityValue,idc.seed_value,increment_value,
CASE idc.system_type_id
	WHEN 127 THEN ((CONVERT(INT, idc.last_value) - CONVERT(INT, idc.seed_value) )+1 )* 100. / 9223372036854775806/CONVERT(INT,increment_value)
	WHEN 56 THEN ((CONVERT(INT, idc.last_value)- CONVERT(INT, idc.seed_value) )+1 )* 100.  / 2147483646/CONVERT(INT,increment_value)
	WHEN 52 THEN ((CONVERT(INT, idc.last_value)- CONVERT(INT, idc.seed_value) )+1 )* 100.  / 32766/CONVERT(INT,increment_value)
	WHEN 48 THEN ((CONVERT(INT, idc.last_value)- CONVERT(INT, idc.seed_value) )+1 ) * 100. / 256/CONVERT(INT,increment_value)
END AS 'PercentageUsed' 
FROM	sys.tables AS t
INNER JOIN sys.identity_columns idc ON idc.object_id = t.object_id
WHERE	idc.is_identity = 1
ORDER BY PercentageUsed DESC

Index Fragmentation on Partitioned tables

2011-04-14T08:47:00.001-07:00

Partitioning can be effectively used to break up a very large table into multiple partitions based on a column or partitioning key. However, there can be significant management overhead in maintaining partitioned tables. The features of partitioned tables that give you the ease of management can also conspire against you to make your maintenance a nightmare!

Thinking of each partition as its own table will help you understand how best to approach your maintenance. Index rebuilds can be quicker because you can rebuild just those partitions that you identify as being fragmented. Conversely, full partitioned Index rebuilds will take longer as each partition is its own b-tree.

Note that old style DBCC SHOWCONTIG command does not work for partitioned indexes. You need to familiarize yourself with the more powerful DMV sys.dm_db_index_physical_stats. This DMV returns fragmentation information at a partition level and provides you the necessary information to make a decision on which partitions require rebuilding or reorganizing.

-- to return fragmentation information on partitioned indexesSELECT
 object_name(a.object_id) AS object_name,
 a.index_id,
 b.name,
 b.type_desc,
 a.partition_number,
 a.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') a
JOIN sys.indexes b on a.object_id = b.object_id and a.index_id = b.index_id
order by object_name(a.object_id), a.index_id, b.name, b.type_desc, a.partition_number

Once fragmentation has been identified we can rebuild the index statement for just that single partition:

--Rebuild only partition 10.
ALTER INDEX IX_alert_events_timestamp
ON dbo.alert_events
REBUILD Partition = 10;
GO

If you're partitioning your data on a date field and you have a sliding window and you are only ever adding data to the right most partitions then you can save unnecessary overhead by not checking for fragmentation of older partitions where you already know they are not fragmented. sys.dm_db_index_physical_stats DMV allows you to specify a partition number. This greatly improves IO over full index scans and has the potential to shorten maintenance job durations.

Find the Size of Database

2011-04-13T08:21:00.001-07:00

I encountered the situation recently where I needed to find the size of the database.

--size of database 
SELECT DB_NAME(database_id) AS DatabaseName,SUM((size*8)/1024) SizeInMB
FROM sys.master_files
GROUP BY DB_NAME(database_id) 
-- size of individual db files
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeInMB
FROM sys.master_files

Best practices for Indexing

2011-03-28T15:49:00.001-07:00

Create index on columns that are frequently used for data retrieval or in filter conditions. For example in SELECT statement and WHERE clause or in ORDER BY, GROUP BY, and DISTINCT clauses. As in our first example, if most of the queries are referencing emp_name column instead of emp_id column, then it is wise to create index on emp_name instead of emp_id i.e. create indexes based on use.
Every table should have a clustered index otherwise it will act as heap table only even if it has non-clustered indexes, resulting performance degradation.
Create indexes on selective columns only that are frequently used.
Try to keep the keys of clustered index as small as possible in size. (i.e. int, smallint)
Optimize cluster index for ranged data i.e. data that are retrieved using BETWEEN, >, >=, <, and <=.
Creative covering indexes are a wise decision for most frequently used queries.
In case of composite indexes, order the columns according to the restrictiveness i.e. smaller quantity first as described in the composite index example above.
Create index on all foreign keys which may be helpful in joining operations.
Use multiple narrow indexes (i.e. on smaller number of columns, may be single column index) rather than a few wide indexes (composite indexes).
Avoid using indexes that are rarely used or else drop them.
Identify poorly performing queries with the help of SQL Profiler, Query Execution plan, and try to optimize it.
Last but not the least; remove indexes while performing BULK insert or heavy insert operations especially in case of Clustered Index as it will perform additional operations for rearranging the indexes for each insertion of the record. Instead, drop indexes before BULK insert and recreate it after insertion operation finishes.

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

2011-02-25T09:13:00.001-08:00

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'

alternate to IF EXISTS ..UPDATE ELSE ..INSERT

2011-02-23T12:15:00.001-08:00

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.

Script to Check and Fix Orphan Users

2011-02-10T08:55:00.001-08:00

-- 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

2011-02-10T08:38:00.001-08:00

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

sys.dm_io_virtual_file_stats

2011-02-07T10:26:00.001-08:00

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

2011-02-07T08:51:00.001-08:00

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

2011-02-07T08:50:00.001-08:00

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

2011-02-07T08:48:00.001-08:00

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'"

What is Database Mirroring

2011-01-20T14:52:00.001-08:00