Made By SQL

How to debug/troubleshoot a SP in production environment?

2011-11-04T12:47:00.000-07:00

There are times when you need to debug

some stored procedure(s) in Production

environment or to make a SP to behave

differently when you execute it via the SSMS

versus case when the SP executed by an application.

Or to make SP to behave differently from requests

that come from different version of an application

or requests that come from specific IPs and etc.

Or you may want to know what values were

provided for the input parameters of the SP?

Here some usefull SQL functions that you can use in the SPs to achieve the above:

1) APP_NAME() - Returns the application name for the current session if set by
   the application. You can set it in the connection string of the application by
   including this: "Application Name=AppName".
   By using this function you can change a SP to do X if it executed from Server A
   and to do Y if it executed by Server Y or if it executed via the SSMS.
   Simply set the application name for different value in Server X and Y.
   By using this function you can also upload SPs changes without any downtime
   and with full backward compatibility.

2) CONNECTIONPROPERTY('client_net_address') - Returns the Client IP address.

3) CONNECTIONPROPERTY('local_net_address') - Returns the IP address of the server.
   For example, if you connect to production SQL server from the SSMS in QA/DEV
SQL Server then it will return the IP of that QA/DEV SQL Server machine.

By using these functions listed above you can add logic (If or CASE statements)
to the SP and you can troubleshoot/debug the SP without running the SQL Profiler.

SELECT APP_NAME() AS Application_Name,
               CONNECTIONPROPERTY('local_net_address') AS local_net_address,
               CONNECTIONPROPERTY('client_net_address') AS client_net_address
GO

How to find stored procedure(s) that scan particular index

2011-08-06T09:40:00.000-07:00

When it comes to performance issues, we can get a lot from querying
the cached plans of stored procedures, for example:

- we can find which stored procedures(SPs) scan an index
- in which SPs we have bookmark lookups/ missing indexes
- find SPs where different spool operators being used
- get SPs which contain parallel execution
- check if an index being used in some SP(s)

and more.

Let`s start with the basic. By using the query below you can get
an execution plan of a stored procedure, open it and analyze it.

-- Get execution query plan in XML format
SELECT OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,cp.usecounts AS ExecutionCount,
                 qp.query_plan AS QueryPlan
FROM     sys.dm_exec_cached_plans AS cp
                 CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
                 CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE cp.objtype='Proc'
                AND st.dbid=DB_ID(DB_NAME()) -- Current DB
                AND OBJECT_NAME(st.objectid,st.dbid) = 'SPName'

By running the next query we can find out which stored procedures
scan particular index.

WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS ns)
SELECT OBJECT_SCHEMA_NAME( qp.objectid ) AS [schema_name],
              OBJECT_NAME(qp.objectid ) AS [object_name],
              qp.query_plan
FROM    sys.dm_exec_cached_plans as p
             CROSS APPLY sys.dm_exec_query_plan( plan_handle ) as qp
             CROSS APPLY qp.query_plan.nodes('//ns:IndexScan/ns:Object[@Database="[DBNAME]"]
                        [@Schema="[dbo]"][@Table="[TblName]"][@Index="[IndexName]"]' ) plan_xml(x)

Important note: The XML is case sensitive, so insert the table/index name
exactly as they defined, pay attention to capital letters.

The following query can help you to check if an index being used,
this can be helpful when you want to delete an index.

WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),

CachedPlansCTE (DatabaseName,SchemaName,TableName,IndexName,QueryText,QueryPlan,[object_name])
AS
(
SELECT ObjNode.op.value(N'@Database',N'varchar(128)') AS DatabaseName,
              ObjNode.op.value(N'@Schema',N'varchar(128)') AS SchemaName,
              ObjNode.op.value(N'@Table',N'varchar(128)') AS TableName,
              ObjNode.op.value(N'@Index',N'varchar(128)') AS IndexName,
              cp.[text] AS QueryText,
              cp.query_plan AS QueryPlan,
              cp.[object_name]
FROM (
           SELECT query.[text],qp.query_plan,OBJECT_NAME(qp.objectid ) as [object_name]
           FROM    sys.dm_exec_cached_plans as p
                        OUTER APPLY sys.dm_exec_sql_text(plan_handle) as query
                        OUTER APPLY sys.dm_exec_query_plan(plan_handle) as qp
           WHERE query.[text] NOT LIKE '%sys%'
                         AND p.cacheobjtype ='Compiled Plan'
                         AND p.objtype='Proc'
) cp
CROSS APPLY cp.query_plan.nodes(N'//Object') ObjNode (op)
)
SELECT [object_name],DatabaseName,SchemaName,TableName,IndexName,QueryPlan,QueryText
FROM    CachedPlansCTE
WHERE (IndexName like '%IndexName%')
              AND (QueryText not like '%insert%')
              AND (QueryText not like '%update%')
OPTION (MAXDOP 1)

Source: http://sqlconcept.com/2011/07/12/how-to-query-the-cached-plans-xml/
http://blogs.sqlserver.org.au/blogs/greg_linwood/archive/2008/04/27/1229.aspx

Slow Transaction Log restore in standby mode (Log Shipping)

2011-08-05T10:19:00.000-07:00

I am always saying that crisis situations
are good for your knowledge.

They can contribute to your
understating how things are working.

Consider the following, you have Log Shipping configured in Standby mode and
you noticed that sometimes the 'Restoring…' takes long time than usual.

So, why restoring 100-200 MB transaction log takes 30-40 minutes on the
secondary server in Logged Shipped database?

In short, due to long running transactions and Standby mode.

Now more in details:

First we need to understand what Standby means and how it works.
When you restore transaction log(s) you have 3 options:

1) WITH RECOVERY
2) WITH NORECOVERY
3) WITH STANDBY

In Log shipping, you can choose between options 2 or 3.

When restoring transaction log(s) with option "RECOVERY", the SQL Server will
undo All uncommitted transactions, the database will be accessible but you will not
be able to restore more transaction logs.

When restoring transaction log(s) with option "NORECOVERY", the SQL Server
will leave the uncommitted transactions as is and you will not be able to access the
database.

When restoring transaction log(s) with the "STANDBY" option, the SQL Server will
go over the transaction log and save all uncommitted transactions to a standby data
file with .tuf extension. (TUF stands for Transaction Undo File). The database will be
accessible and in read-only mode.

You can ask why it needs the tuf file. Well, when you restore with STANDBY mode
the database stays accessible, right? Meaning you can connect to it and view the
data (just read, because it in Read-Only mode).
But how it possible, what about uncommitted transactions?

You will not see the uncommitted data even if you read the data in
"READ Uncommitted Isolation Level". Thus is, because the SQL Server
performs UNDO for all uncommitted transactions and saves their data to
the tuf file. It simply saves the entire content of the pages that it had to undo
and in some cases the SQL Server can save not only the data rows but the all pages.

The tuf file will be used when you restore the next transaction log file.
First, the SQL Server will REDO all the uncommitted transactions that in the tuf file
(apply the content of the undo file to the data files) and only then continue to restore
the next transaction log.

Important note: the tuf file will contain the uncommitted transactions not just from
the latest transaction log but uncommitted transactions in all previous transaction
logs that have been restored.

Now after we understand how the STANDBY option works, let`s see what happening
when you rebuild indexes in the primary server and suppose that the rebuild of some
table spreads over more than one backed up transaction log. For example:
the rebuild takes 20 minutes and you backup the transaction log every 5 minutes.

I think you already got it. The secondary server upon restoring the first transaction
log that contains the start of the index rebuild will save the uncommitted data to
the tuf file, in our case, if we rebuild clustered index, it will save a lots of data to
the tuf file. And upon restoring the next transaction log, it will redo all the
uncommitted transactions saved in the tuf file and continue the restoring, but in
our case the index operation is not finished in the second transaction log file,
so SQL Server will again UNDO the rebuild index and save it to the standby file
(tuf file), now it will contain uncommitted data of the 2 transaction logs. And this
process continues and continues until the last transaction log with index rebuild
data will be restored.

I hope it helped you to understand the reason for the slow restore of the transaction
logs in the secondary logged shipped database.

Thanks for reading.

4 ways to efficiently monitor DB Mirroring with/without email/SMS Alerts

2011-08-03T13:00:00.000-07:00

A setup of DB Mirroring (See my post here) cannot be full without
a setup of DB Mirroring monitoring.
I want to share with you 4 ways to monitor the DB Mirroring:

1) Querying the "sys.database_mirroring" catalog view and sending Email Alerts.
2) Using SQL Server Events Notifications, State-change (WMI) events.
3) Setting up Performance Threshold Events in the "Database Mirroring Monitor".
4) Setting up SQL Performance counters related to DB Mirroring.

Now more in details:

1) Querying the "sys.database_mirroring" catalog view and sending Email Alerts

We can query the sys.database_mirroring catalog view for checking the status
of the mirrored databases, and in case the DB status is not in valid state, like
SYNCHRONIZED or SYNCHRONIZING, we will send an email and/or SMS.

DECLARE @DBMirroringState VARCHAR(30),@DB_ID INT,@ErrorMessageToSend VARCHAR(100)
DECLARE @MirroredDatabases TABLE (DatabaseID INT, mirroring_state_desc VARCHAR(30))

-- get status for mirrored databases
INSERTINTO @MirroredDatabases(DatabaseID,mirroring_state_desc)
SELECT database_id, mirroring_state_desc
FROM [sys].[database_mirroring]
WHERE mirroring_role_desc IN ('PRINCIPAL','MIRROR')
             AND mirroring_state_desc NOT IN ('SYNCHRONIZED','SYNCHRONIZING')

WHILE EXISTS (SELECT TOP 1 DatabaseID FROM @MirroredDatabases WHERE mirroring_state_desc IS NOT NULL)
BEGIN
     SELECT TOP 1
                   @DB_ID = DatabaseID,
                   @DBMirroringState = mirroring_state_desc
     FROM    @MirroredDatabases

    SET @ErrorMessageToSend = 'DBMirroring Error on DB:'+CAST(DB_NAME(@DB_ID) AS VARCHAR)+
                                           ',DBState='+@DBMirroringState

   -- Send Email
   EXEC msdb.dbo.sp_send_dbmail @profile_name='XXXXX',@recipients='DBA@Company.com',
                                              @body = @ErrorMessageToSend,@subject = @ErrorMessageToSend
-- Send SMS
-- put here code to send SMS

   DELETE FROM @MirroredDatabases WHERE DatabaseID = @DB_ID
END

Source: http://www.mssqltips.com/tip.asp?tip=1859

--------------------------------------------------------------------------------------------------------------------------------------------------------------
The second way is:
2) Using SQL Server Events Notifications, State-change (WMI) events
--------------------------------------------------------------------------------------------------------------------------------------------------------------
There is a way in the SQL Server to create an Alert for various inner events.
We will create an alert for specific DB mirroring events.

When a mirroring session changes from one state to another, SQL Server generates
a WMI event of type DATABASE_MIRRORING_STATE_CHANGE.
We will create WMI-based alert by querying the DATABASE_MIRRORING_STATE_CHANGE.

For example:
SELECT * FROM DATABASE_MIRRORING_STATE_CHANGE WHERE State = 6

In the Principal Server we will check for following events:

   - Connection with Mirror Lost
   - Mirroring Suspended

In the Mirror Server for following alerts:

- Connection with Principal Lost
- Mirroring Suspended

You can setup such alerts via the SSMS GUI or by executing a script.
In SSMS, go to "SQL Server Agent", right click on Alerts and choose "New Alert"
and define it as follow, for example:

And do not forget to specify to send the alert email:

or you can run a script, for example:

USE [msdb]

GO
EXEC msdb.dbo.sp_update_alert
         @name=N'DB Mirroring State Change: Connection with Mirror Lost',
         @message_id=0,
         @severity=0,
         @enabled=1,
         @delay_between_responses=0,
         @include_event_description_in=1,
         @database_name=N'',
         @notification_message=N'',
         @event_description_keyword=N'',
         @performance_condition=N'',
         @wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',
         @wmi_query=N'SELECT * FROM DATABASE_MIRRORING_STATE_CHANGE WHERE State = 6',
         @job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_update_notification
         @alert_name=N'DB Mirroring State Change: Connection with Mirror Lost',
         @operator_name=N'DBA', @notification_method = 1
GO

Source: http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/how-to-monitor-database-mirroring

3) Setting up Performance Threshold Events in the "Database Mirroring Monitor"

With these alerts, most of us should be familiar.
We can setup the thresholds in the "Database Mirroring Monitor":

As the result, SQL server will create "Database Mirroring Monitor Job" on
Principal and Mirror servers. This job will run every minute (by default) and update
undocumented database mirroring status table in the msdb database.

Now, after setting the thresholds, you need to create alert(s) using SQL Server Agent:

In SSMS, connect to the principal or mirror server instance for which you want to define an alert.
Expand the SQL Server Agent folder in Object Explorer.
Right-click Alerts and select New Alert.
In the New Alert dialog box, type a name you can use to identify the alert, such as "DB Mirroring: Unsent log warning."
Select Error number and type the number from Table below.
In Select a page, click Response to specify the actions to take when the event occurs, such as executing a job or notifying an operator.
In Select a page, click Options to specify how often to send a notification on this event, the format of the message sent to operators, and other options.

Event IDs for Performance Warnings :

The first two warnings in table above should be defined in the Principal Server,
the rest in the Mirror Server.

Now very important note that can save you time,

When you define such alerts, do not specify the database, choose "All Databases",

Otherwise the alert will not occur (not fire) and the email will not be sent.
The SQL Server will write the alert to the Windows Event log and to
SQL Server Error Log, but SQL Server will not be able to recognize it
and will not raise the alert, because it has some bug recognizing such alerts

where database name was specified.
(The event logged as a server-level event and not a database-level event)

I have this bug in SQL Server 2008 R2 ENT with CU6 and Microsoft does not
provided a workaround yet.

http://connect.microsoft.com/SQLServer/feedback/details/657230/alerting-on-database-mirroring-events

If you have more that one mirrored database in one SQL Server instance and you
want to define the alerts per database, then you will have a problem when you
choose "All Databases".

Also when the alert defined with "All Databases", an email is sent without

specifying the database that exceeded its threshold.

4) Setting up SQL Performance counters related to DB Mirroring.

We can fetch SQL performance counters related to DB Mirroring from DMV called

"sys.dm_os_performance_counters".

I recommend to fetch following counters:

- Redo Queue KB : The number of bytes of transaction log that remains to be

applied to the mirror to roll it forwards.

- Log Send Queue KB: The number of bytes in the log that has not been sent to

the mirror

SELECT [counter_name] as CounterName,[cntr_value] as CounterValueFROM sys.dm_os_performance_counters

WHERE [object_name] LIKE ('%Database Mirroring%')

AND [counter_name] IN ('Log Send Queue KB','Redo Queue KB')

AND [instance_name]='TestDB'

For example, you can query this DMV every minute and save the values in CACTI.

In the Cacti you will see nice graphs:

This method is for long run monitoring, to recognize trends and etc.

If you have other way, please share.

One more thing that good to know and understand:

In asynchronous mirroring the transaction log can fill up as transactions
cannot be backed up until they have been applied on the mirror.

For more info:

http://msdn.microsoft.com/en-us/library/ms365781.aspx

http://msdn.microsoft.com/en-us/library/ms408393.aspx

http://technet.microsoft.com/en-us/library/cc966392.aspx

SQL Jokes cont.

2011-07-22T12:04:00.000-07:00

To continue the of SQL Jokes below

http://madebysql.blogspot.com/2009/07/sql-joke.html

http://madebysql.blogspot.com/2010/02/types-of-dbas.html

Here are some more:

-----------------------------------------------------------------------------------
Q: Why do you never ask SQL people to help you move your furniture?
A: They sometimes drop the table
-----------------------------------------------------------------------------------

-----------------------------------------------------------------------------------
Q: How do you spell GOD?
A: DBA
-----------------------------------------------------------------------------------

-----------------------------------------------------------------------------------
Q: When did God create the DBA ?
A: The day before he had his rights revoked.
-----------------------------------------------------------------------------------

-----------------------------------------------------------------------------------
Q: Why do DBAs often confuse Halloween and Christmas?
A: Because '31 Oct' = '25 Dec'
-----------------------------------------------------------------------------------

-----------------------------------------------------------------------------------
Q1: What did the DBA say to the Developer?
A: It doesn’t matter, he wasn’t listening anyway.
Q2: What did the Developer say to the DBA?
A: It doesn’t matter, the answer was no.
-----------------------------------------------------------------------------------

-----------------------------------------------------------------------------------
Joins are what RDBMS's do for a living.
-----------------------------------------------------------------------------------

-----------------------------------------------------------------------------------
"NULL is the Chuck Norris of the database - nothing can be compared to it."
-----------------------------------------------------------------------------------

-----------------------------------------------------------------------------------
DBA = Default Blame Acceptor
-----------------------------------------------------------------------------------

Do you have more? You are invited to share.

Is the % of an index fragmentation in Log shipped / Mirror database equal to the Primary/Source database?

2011-07-22T06:03:00.000-07:00

Suppose there is Log shipping or Mirror between database X and database Y.
The mdf/ldf file(s) of the X DB and Y DB located in different storages.
I take some index in the primary db X and estimate its fragmentation by
using “dm_db_index_physical_stats” and do the estimation of that index
also in database Y.

SELECT  dm.object_id AS objectid,
              dm.index_id AS indexid,
               QUOTENAME(i.name) AS name,
               dm.partition_number AS partitionnum,
               dm.avg_fragmentation_in_percent AS Fragmentation,
               i.fill_factor AS [Fillfactor],
               dm.page_count,
               CASE WHEN dm.alloc_unit_type_desc <> 'IN_ROW_DATA'
                        OR (t.lob_data_space_id = 1 AND dm.index_type_desc <> 'NONCLUSTERED INDEX')
                     THEN 'OFF' ELSE 'ON'
              END as [Online]
FROM    [sys].[dm_db_index_physical_stats] (DB_ID(), NULL, NULL , NULL, 'LIMITED') dm
              INNER JOIN [sys].[tables] t ON dm.object_id = t.object_id
              INNER JOIN [sys].[indexes] i ON dm.object_id = i.object_id AND dm.index_id = i.index_id
WHERE dm.avg_fragmentation_in_percent > 0
              AND dm.page_count > 1000
              AND dm.index_id > 0

Let`s assume that the db X and Y are fully synchronized,
meaning all transactions from db X were transferred to db Y.

The question is: Is the % of the index fragmentation equal in db X and Y?

The answer is Yes.
Because every log record gets replayed on the secondary.
Every page split and every allocation is fully logged and then replayed
exactly the same on the secondary.

You may ask, what for you need to know the % of fragmentation
on secondary server. Well, first of all, estimation of the % of fragmentation
by using the “dm_db_index_physical_stats” in Production site may cause to
IO load. So we can estimate the fragmentation on the secondary server.
Secondary, it is always good to know and understand how it is working.

Here more info:
http://www.sqlsoldier.com/wp/sqlserver/whatdoesredonemean

Database Mirroring between a Server in Domain and Server outside domain (workgroup)

2011-06-25T03:49:00.000-07:00

When you setup a Database Mirroring via the GUI in SSMS, you are
requested to specify the fully qualified domain names (FQDN) of the servers.
What about the cases when you need to configure the Database Mirroring

between two servers that not in a domain at all or the 2 servers on different

domains with no trust relationships or one server in domain and

the second is not. The setup of a DRP site whould be classic example.
You can enter IP addresses in the GUI wizard or

replace the IPs with some Server names that you put in the HOST file

it will work only if both servers in the same domain, in other cases you will

probably fail to setup the Mirroring, because SQL Server will try to use

Windows Authentication between the servers (By default the Configure

Database Mirroring Security Wizard always uses Windows Authentication)

You will get an error like this:

The server network address“TCP://SQLServer:5023″ cannot be reached or

does not exist.Check the network address name and that the ports for the local

and remote endpoints are operational.(Microsoft SQL Server, Error: 1418)

So how to setup the Database Mirroring when the two Servers not in the same

domain or the two servers are workgroups or one server in a domain and other not?

Well, SQL Server allows configuring database mirroring under mixed mode

authentication using SQL Server logins with the added security of using certificates.

In English, you will have to create SQL Server Logins/Database Users and

the Mirroring EndPoint with authentication that use Certificate and not

Windows Authentication.

Note: The explanation below is dealing with mirroring configuration without
the witness server, please refer to links below if your configuration includes
witness server, in general all the steps for Principal/Mirror server should be
done also for the witness server.

Here is the highlight summary of the steps:
All the steps below should be done in each server instance(Principal/Mirror)
SQL scripts for each step you can find at the end,
and I suppose the you already restored the DB (and transaction logs, if needed)
in Mirror SQL Server with NORECOVEY of course.

1.In the master database, create a database master key.
2.In the master database, create an encrypted certificate.
3.Create a mirroring endpoint using the created certificate.
4.Back up the certificate to a file and copy it to the other server.

After you complete the steps above in both servers, do the following
also in both servers in master database:

5.Create a SQL Server login in each Server
6.Create a database user for created login.
7.Associate the certificate with the database user created in step 6.
8.Grant CONNECT permission on the SQL Server login for created
mirroring endpoint.

Now let`s see the SQL Scripts,
note that the scripts have different numeration then general steps above.

-------------------------------------------------------------------------------------------------------
/* Execute this against the Principal Instance */
-------------------------------------------------------------------------------------------------------
-- Step 1 :
--                    CREATE MASTER KEY,CREATE CERTIFICATE
--                   CREATE ENDPOINT,BACKUP CERTIFICATE to file
---------------------------------------------------------------------------------
USE MASTER
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'somepassword'
GO
--Create the server-based certificate which will be used to encrypt the database mirroring endpoint
CREATE CERTIFICATE PrincipalServerCertificate
WITH SUBJECT = 'PrincipalServer certificate',
START_DATE = '20110601',
EXPIRY_DATE = '20500101'
GO
--Create the database mirroring endpoint for the principal server using the certificate for authentication
CREATE ENDPOINT MirroringEndPoint
STATE = STARTED AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING(AUTHENTICATION = CERTIFICATE PrincipalServerCertificate
,ENCRYPTION = REQUIRED ALGORITHM RC4,ROLE = ALL)
GO
BACKUP CERTIFICATE PrincipalServerCertificate
TO FILE = 'c:\PrincipalServerCertificate.cer'
GO

--------------------------------------------------------------------------------------------------------------------------
/* Execute this against the Mirror Instance */
---------------------------------------------------------------------------------------------------------------------------
-- Step 2 :
--              CREATE MASTER KEY,CREATE CERTIFICATE,
--              CREATE ENDPOINT,BACKUP CERTIFICATE to file
----------------------------------------------------------------------------------
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'somepassword'
GO
--Create the server-based certificate which will be used to encrypt the database mirroring endpoint
CREATE CERTIFICATE MirrorServerCertificate
WITH SUBJECT = 'MirrorServer certificate',
START_DATE = '20110601',
EXPIRY_DATE = '20500101'
GO
CREATE ENDPOINT MirroringEndPoint
STATE = STARTED AS TCP (LISTENER_PORT = 5023, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE MirrorServerCertificate,
ENCRYPTION = REQUIRED ALGORITHM RC4,ROLE = ALL)
GO
BACKUP CERTIFICATE MirrorServerCertificate TO FILE = 'c:\MirrorServerCertificate.cer';
GO

------------------------------------------------------------------------------------------------------------------------
/* Execute this against the Principal Instance.
     The MirrorServerCertificate.cer needs to be copied on the Principal Server.
*/
-------------------------------------------------------------------------------------------------------------------------
-- Step 3 :
--               CREATE LOGIN,CREATE USER,CREATE CERTIFICATE (from Mirror server)
--               and grant the created User to it.
------------------------------------------------------------------------------------------------------------------------
USE MASTER
GO
CREATE LOGIN MirrorLogin WITH PASSWORD = 'somepassword'
GO
CREATE USER MirrorUser FOR LOGIN MirrorLogin
GO
CREATE CERTIFICATE MirrorServerCertificate AUTHORIZATION MirrorUser
FROM FILE ='c:\MirrorServerCertificate.cer'
GO
GRANT CONNECT ON ENDPOINT::MirroringEndPoint TO [MirrorLogin]
GO

/* Execute this against the Mirror Instance.
    The PrincipalServerCertificate.cer needs to be copied on the Mirror Server.
*/
----------------------------------------------------------------------------------------------------------------------
-- Step 4 :
--                CREATE LOGIN,CREATE USER,CREATE CERTIFICATE (from Principal server)
--                and grant the created User to it.
----------------------------------------------------------------------------------------------------------------------
USE MASTER
GO
CREATE LOGIN PrincipalLogin WITH PASSWORD = 'somepassword'
GO
CREATE USER PrincipalUser FOR LOGIN PrincipalLogin
GO
CREATE CERTIFICATE PrincipalServerCertificate AUTHORIZATION PrincipalUser
FROM FILE = 'c:\PrincipalServerCertificate.cer'
GO
GRANT CONNECT ON ENDPOINT::MirroringEndPoint TO [PrincipalLogin]
GO

/* Execute this against the Mirror Instance.*/
------------------------------------------------------------------------------------------------
-- Step 5 :
--             Prepare the mirror server for the database mirroring session
------------------------------------------------------------------------------------------------
ALTER DATABASE TESTDB
SET PARTNER = 'TCP://PrincipalServerIP5022'
GO

/* Execute this against the Principal Instance.*/
-------------------------------------------------------------------------------------------------
-- Step 6 :
--            Prepare the Principal server for the database mirroring session
-------------------------------------------------------------------------------------------------
ALTER DATABASE TestDB
SET PARTNER = 'TCP://MirrorServerIP:5023'
GO
-- Set the Mirroring to Asynchronous mode (if needed)
ALTER DATABASE TestDB SET PARTNER SAFETY OFF
GO

That`s all after Step 6 the mirroring shoul be initialiazed.

Troubleshooting:

1) Ensure the Principal and Mirror Server listening to defined mirroring endpoints.
     They should be listening after you define the endpoints and their state is Started.
      You can use the following command in Principal Server
       netstat -na | find "5022"
       and this in Mirror Server
       netstat -na | find "5023"

       Of course, you can set the port numbers to be different then in this example.

2) Make sure firewall is not blocking the port and the firewall is allowing traffic both directions

3) Check the SQL log for errors

Mirroring Monitoring problems:

In our case, which is, Principal Server in a Domain and the Mirror Server
is not, when launching the "Database Mirroring Monitor" we had the following error:


If the Monitor was executed in the Principal Server, then there was an error
saying that the Mirror Server not connected. And vice versa when the Monitor
was launched from Mirror Server.
Going to "Manage Server Instance Connections" option

and choosing SQL Authentication in the connection of Mirror Server not helped,
it gave the following errors

or
"SQL Server replication requires the actual server name to make a connection to
the server. Connections through a server alias, IP address, or any other alternate
name are not supported."

It seems that SQL Server tries to connect to the Mirror server by using
the Server Name and not IP.
Adding the mapping to HOST file solved the problem.

---------------------------------------------------------------------------------------
To see how to monitor the DB Mirroring from every angle,
please my post here.
---------------------------------------------------------------------------------------
Also, I want to share the Clean up scripts,
they are needed when the mirroring is not working and you want to start
over again or if you are not sure in each step you stopped and etc.

-----------------------------------------------------------------------
-- Clean up / check scrips for the PRINCIPAL/MIRROR SQL Server
-----------------------------------------------------------------------
/*
------------------------------------------------------------------------------------------------------------
Drop master key
----------------------------------------------------------------------------------------------------------

ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY

-- check if the database master key is encrypted by the service master key.
SELECT is_master_key_encrypted_by_server ,*
FROM sys.databases
WHERE name='master'

select name, principal_id, algorithm_desc, create_date
from master.sys.symmetric_keys
where name like '%databasemaster%'

DROP MASTER KEY

--------------------------------------------------------------------------------------------------------
-- DROP ENDPOINT
--------------------------------------------------------------------------------------------------------

DROP ENDPOINT MirroringEndPoint

SELECT name, role_desc, state_desc
FROM sys.database_mirroring_endpoints

--------------------------------------------------------------------------------------------------------
-- DROP CERTIFICATE
--------------------------------------------------------------------------------------------------------

DROP CERTIFICATE PrincipalServerCertificate
DROP CERTIFICATE MirrorServerCertificate

SELECT *
FROM sys.certificates

-------------------------------------------------------------------------------------------------------
-- DROP USER
-------------------------------------------------------------------------------------------------------

DROP USER MirrorUser

SELECT * FROM sys.sysusers;

--------------------------------------------------------------------------------------------------------
-- DROP LOGIN
-------------------------------------------------------------------------------------------------------

DROP LOGIN MirrorLogin

SELECT * FROM sys.server_principals

*/

For more information see following links
http://technet.microsoft.com/en-us/library/ms191477.aspx

http://www.mssqltips.com/tip.asp?tip=1705

http://blogs.msdn.com/b/john_daskalakis/archive/2009/01/15/9320483.aspx

@@Rowcount and Aggregative functions

2011-06-07T05:35:00.000-07:00

Please note the following,

When using aggregative function(s) in a SELECT that not returns any row
you will always get one row in the result set and @@RowCount will be always
be equal to 1.

Let`s see an example:

CREATE TABLE TestTable (RowID INT NOT NULL)
GO

-- Fetch rows without aggregative function in the Select
SELECT * FROM TestTable

SELECT @@ROWCOUNT as RowsCount

As you see, the result of @@ROWCOUNT is 0 as expected,
Now let`s see what will happened if we add a aggregative function to the
SELECT, for example, COUNT/AVG and etc.

CREATE TABLE TestTable (RowID INT NOT NULL)

GO

-- Fetch rows with aggregative function in the Select
SELECT AVG(RowID) FROM TestTable
SELECT @@ROWCOUNT as RowsCount

The @@RowCount is 1, even the table is empty.
So, when writing logic that depends on the result of @@RowCount,
keep in mind the case described above.

Interesting index option IGNORE_DUP_KEY

2011-05-06T10:33:00.000-07:00

In one of migration scripts, we needed
to create a table to hold distinct values.
In the population process, there was no
option to specify 'SELECT DISTINCT',
because the population was done by batches.
Instead of checking if a value already exist
in a target table, we created an unique index
with IGNORE_DUP_KEY option ON.

Additional example will be related to ETL process,
For example, if you have different sources of data
Or same row can come more than once from one source,
You can use this index option on ODS/Delta tables.

So, what this option does? When this index option turned off (by default) and
you try to insert duplicate value to a column with uniquie index, you will get an
error something like this:
Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row .........

In case when you create an index(or alter it) with IGNORE_DUP_KEY=ON,
the duplicate values will be not inserted and you will get warning message:
Duplicate key was ignored.

Here the example:
CREATE TABLE TestTbl(Name VARCHAR(10))
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_Name]
ON TestTbl (Name ASC) WITH (IGNORE_DUP_KEY = ON)
GO

INSERT INTO TestTbl(Name)
SELECT 'Dan' UNION ALL
SELECT 'Tim' UNION ALL
SELECT 'Dan'
GO
SELECT * FROM TestTbl

Note: On UPDATE command this feature do NOT work,
you will get an error. So be aware of this option,
Thanks for reading.

Error when running SQL Server 2005 SSIS package on SQL Server 2008

2011-04-19T10:41:00.000-07:00

If you have upgraded from SQL Server 2005 64 bit to

SQL Server 2008 64 bit and you execute SSIS package that

you had in SQL Server 2005 you will get error something like this:

Description: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_64BIT_ERROR.
The requested OLE DB provider SQLNCLI.1 is
not registered -- perhaps no 64-bit provider is available. Error code: 0x00000000......................................................................................................

This is because the provider name for SQL Native Client (SNAC)
connections has changed in SQL Server 2008 and the SQL 2005
version of this provider is not installed with SQL 2008 by default.

Version Provider name:

SQL Server 2005 SQLNCLI.1

SQL Server 2008 SQLNCLI10.1

Meaning you have to change the connection string to use the
SQLNCLI10.1 instead of SQLNCLI.1

Here is the reference.

How to upgrade to SQL Server 2008 R2 with minimal downtime: 1-2 minutes

2011-04-18T11:39:00.000-07:00

Here is real life example,
how to upgrade SQL Server 2005
to SQL Server 2008 R2 with
less than 2 minutes downtime
and without any connection
string changes.
Sounds interesting?
Then keep reading.

Site configuration:
Active/Passive failover cluster with SQL Server 2005
Enterprise Edition.

Generally, this is the way:
Break Cluster, leave one node in the cluster(Node X),
install new cluster on second node Y,
setup SQL 2008 in the second node Y,
setup mirroring between node X and node Y,
do mirroring failover,switch between the clusters
(assign IP of original cluster to the new cluster),
install SQL 2008 on Node X, join Node X to the new cluster.

Below is high level work plan step by step,
I will not elaborate on system related aspects,
I will just mention the steps:

Let suppose Node X is Active.

1) Break the cluster, remove passive node Y from the cluster

2) If you have Domain Controller installed on Node X/Y machine,
    you will have to remove it, because SQL 2008 cannot be
    installed on Domain Controller machine, you will need to
    install the DC on other machine.
    Remove the Node Y from old domain, Join it to new domain.

3) Define new Cluster on Node Y with different name.

4) UnInstall SQL Server 2005,Install SQL Server 2008 R2 on Node Y
   (Cluster installation type)
    Create Logins with Same SIDs,Jobs and etc.

5) Setup Mirroring between Node X and Node Y, in async or sync
    mode, yep mirroring between SQL 2005 and 2008, it works fine

6) Do manual Mirroring failover (recommended changing to
    synchronized mode before the failover). Remove the Mirroring.
    2-5 seconds downtime.

7) Switch the Clusters, assign the IP of old cluster to the new
    Cluster, change the DNS records.
    1 minute downtime.

8) That`s all, the SQL is up and ready to get connections.
     As you can see, there is no need to change any connection
     strings in the applications.

9) Now complete work on Node X, UnInstall SQL 2005,
    install SQL 2008, join it to new Cluster.

Again, this is in high level.

Important notes:

a. When you finish the manual Mirroring failover, there is no way
   back to SQL Server 2005.

b. Check if you can allow some time period without Cluster protection.

Thanks for reading

BI Developer Interview Questions

2011-04-15T14:57:00.000-07:00

I want to share with you set of interview
questions relevant for BI Developer.
What do I mean by saying BI Developer?
A person that understands SQL,
knows how to build efficient ETL process,
knows how to design a Datawarehouse,
knows how to design and build cubes,
understands how BI works, knows to
provide design of ETL/DWH/Cubes from
analyzing the business requirements.

0) Describe the BI system you have in your company, including
    ETL process. What were your responsibilities in your last job ?
    What are the challenges you faced in your BI system ?

-----------------------------------------------------------------------
-- SQL knowledge ---------------------------------------------------
-----------------------------------------------------------------------

1) What is the difference between a Clustered and NonClustered
      index? For what type of queries each index is good?

2) Write at least two different queries(different ways) that return
     from the table below, the 2 most latest released books for each Author.

Note: the table above may contain more rows for each author.
------------------------------------------------------------------------

3) Why OLTP database design not generally a good idea for
    a Database Warehouse?

4) What type of schemas you know in DataWarehouse DBs?
    What are the differences? Is there any performance differences?

5) What are fact and dimension tables?

6) What is Dimensional Hierarchy in OLAP ? Why we need it?

7) If we need to limit the access to a data in a cube,
    How do you do it?

8) What are different storage mode options in SSAS?
     How do you decide when to use each of them?
    What are the differences?
    What the Advantages and Disadvantages of each technology?
    How they effect on performance?

9) If you need to create your own measure that holds some logic,
    How do you do it?
    What are Additive, Semi additive and No additive measures?

10) How would you optimize the dimensions?

11) What really ‘Optimize schema’ option is SSAS does?

12) Can you give an example when you need to create
      two measure groups?

13) What is the difference between attribute hierarchy and user hierarchy?

-----------------------------------------------------------------------------
-- SSIS related questions ---------------------------------------------------
-----------------------------------------------------------------------------

14) What is Lookup is SSIS?

15) Is it possible to restart failed SSIS package from the point of
      failure, instead of rerunning the whole package?
      How to implement this?

16) Is it possible to define breakpoints in SSIS package?
      If yes, how it helps the developer?

17) Is it possible to define transaction in package/container or
      Control Flow task level in a SSIS package? How you do it?

18) What's the difference between Control Flow and Data Flow?

----------------------------------------------------------------------------
-- MDX related questions -------------------------------------------------
----------------------------------------------------------------------------

19) What are the differences between MDX and T-SQL?

20) How can you retrieve Top 10 customers without using
      TopCount function?

If you need the answers to any question above,LMK.

Order of Joins, FORCE ORDER Hint

2011-04-01T07:25:00.000-07:00

Well, as you may know SQL Server can build the execution plan
with different order of joins than in your query. This is done as
part of the query execution optimization.

I had a situation when a query did not performed well, the
execution plan was not optimal. In such cases, what you
should do, is to check whether the statistics are updated,
check if SQL Server uses right indexes,check for missing
indexes and maybe you need to rewrite your query.

In my situation, I had no time
to rewrite the query, but we
noticed that the order of tables
that SQL choses to accessed
was not optimal.

The quick and dirty solution was to add 'Force Order' hint to the query.
What is does? It specifies that the join order indicated by the query
syntax should be preserved during query optimization.

In most cases the SQL Server optimizer will do great job.
So you should use this hint only in exceptional cases.

How to resolve references to system objects in TFS, References to Master/MSDB objects

2011-03-29T11:24:00.000-07:00

To continue of TFS/DB Dude subject, I want to share with you
some issues we had with starting to work with the TFS Database
edition.

This post will discuss "How to resolve references to system objects
in TFS, References to Master/MSDB objects". In next posts
I will describe additional problems, like merge issues, adding
new objects to DB and etc.

Probably you use system objects in your stored procedures,
For example: system tables, system views, DMVs or system
catalogs or you use MSDB objects in your SPs, like sending
emails from DB.

In such cases, when you
run a build of the solution
or database project, you
will get warnings indicating
that the system objects used
in the stored procedures
cannot be resolved.

Here some examples:

XXX has an unresolved reference to object [sys].[schemas].
XXX has an unresolved reference to object [sys].[tables].
XXX has an unresolved reference to object [sys].[dm_db_index_physical_stats].
XXX has an unresolved reference to object [msdb].[dbo].[sp_send_dbmail].

The solution :

To reference a master or msdb database containing system
objects, you can reference to a master.dbschema file or
msdb.dbschema file accordingly to resolve the references
to the system objects.

These files can be found in :

C:\Program Files\Microsoft Visual Studio 10.0\VSTSDB\
Extensions\SqlServer\2008\DBSchemas\

The red values can vary, depending of you version of TFS
and DB project.
Note that each supported version of SQL Server has
corresponding master.dbschema and msdb.dbschema files.

You may see problems such as 'master.dbschema does not exist'
when you do Build from other machines, for example,
from TFS Server.
Or when you do a Branch.

Error Example:

C:\Program Files\MSBuild\Microsoft\VisualStudio\v10.0\TeamData\Microsoft.Data.Schema.TSqlTasks.targets (56): File D:\Builds\NeoApp\Program Files\Microsoft Visual Studio 10.0\VSTSDB\Extensions\SqlServer\2008\DBSchemas\master.dbschema does not exist.

This happens because the path to these files saved in dbproj file
by using relative path, something like this \..\..\xx\xxx\x

One way of dealing with this problem is to make your referenced
dbschema files a part of the solution and reference using
a relative path. For example, create a folder in your
Solution/Project, copy files into it and create the reference to
these files in the folder. This will solve the path issues
between machines.

Finally, here is a link to good article about the
Microsoft Visual Studio Team System 2008 (VSTS) Database Edition

Search for a string in SQL Agent Job Steps

2011-03-18T06:29:00.000-07:00

When you change schema of tables or
when you change a SP, you probably need to
to find out whether the SP used in SQL Server
Job(s) or there are inline SQL statements in
the Jobs that use your table(s).

How to do it quickly?

SELECT js.database_name as DatabaseName,
                 jobs.Name as JobName,
                 js.step_id as StepID,
                 js.step_name as StepName,
                 js.command as StepCommand
FROM     msdb.dbo.sysjobs as jobs
                INNER JOIN msdb.dbo.sysjobsteps as js ON jobs.job_id = js.job_id
WHERE jobs.[enabled] = 1 AND js.command LIKE '%XXXXXXXXX%'
ORDER BY jobs.Name,js.step_id

Thanks for reading.

A table variable is not a memory-only structure

2011-03-11T11:54:00.000-08:00

Yep, the word 'Variable' is misleading.

According to Microsoft article:

"A table variable is not a memory-only structure.

Because a table variable might hold more data than

can fit in memory, it has to have a place on disk to store data.

Table variables are created in the TempDB database similar

to temporary tables. If memory is available, both table variables

and temporary tables are created and processed while

in memory (data cache). "

We can easily check this by checking the 'sysobjects'

table in the TempDB:

DECLARE @TestTable TABLE(BookID INT)

SELECT TOP 1 *

FROM tempdb.sys.sysobjects

WHERE [name] LIKE '#%'

AND [name] NOT LIKE '%[_]%'

ORDER BY crdate DESC

Thanks for reading.

Off Topic: Rogun (Roghun) town,Tajikistan

2011-02-25T08:41:00.000-08:00

There is a place in the World called Rogun (Roghun) town
(Город Рогун, Таджикистан).
It is a small town in Tajikistan. This is the place I grew up.
Near it town called Sary Pulak (Сары-Пулак).
I put this post for memory. If you somehow read this post
and you know these cities,maybe you have new photos,
let me know.

What a beauty! Rogun town, Город Рогун, Таджикистан.

and this is Sary Pulak, Сары-Пулак.

This boy is me !

How to use performance counters in sys.dm_os_performance_counters DMV

2011-01-26T09:40:00.000-08:00

As you probably already know,
in DMV called 'sys.dm_os_performance_counters' you can find
many performance counters related to the SQL Server.
You can use them instead of using the counters from Windows
Performance Monitor application.

But there is a catch you should be aware of.

There are 3 types of the counters and a counter`s value
calculation depends on the type of the counter.

Counters types are:

Value/Base:
In order to get the right value of this kind of counter you have
to take counter with cntr_type = 537003264 and divide it by
'base' counter with cntr_type = 1073939712.

For example:
("Buffer cache hit ratio" / "Buffer cache hit ratio base") *100
will give you the 'Buffer cache hit ratio'

SELECT object_name,counter_name,cntr_value,cntr_type
FROM sys.dm_os_performance_counters
WHERE (counter_name = 'Buffer cache hit ratio'
OR counter_name = 'Buffer cache hit ratio base')
AND object_name like '%:Buffer Manager%'

Per Second:
These kind of counters store cumulative values, meaning the
value must be compared at 2 diffrerent times by calculating the
differences between the values.For instance,get a counter value,
save it, then get it again after 5 seconds for example and the right
final counter value will be:
(@SecondCounterValue-@FirstCounterValue)/5 seconds.

These counters have cntr_type = 272696576.

SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name = 'SQL Compilations/sec'
              AND object_name like '%SQL Statistics%'

Point In Time:
These are point-in-time counters.They hold the value at the
current point-in-time.These counters have cntr_type = 65792.

SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name = 'User Connections'
              AND object_name Like '%General Statistics%'

Example for calculating the Buffer cache hit ratio:

SELECT (a.Val*1.0/b.BaseValue)*100.0 as [BufferCacheHitRatio]
FROM (
SELECT cntr_value as Val
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio'
               AND object_name like '%Buffer Manager%'
) as a
CROSS JOIN
(
SELECT cntr_value as BaseValue
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio base'
               AND object_name like '%:Buffer Manager%'
) as b

By knowing the types of the counters, you can write a SP
that will collect for you the performance data of your server.
Here is a very good example of such SP.

TFS and SSMS integration

2010-12-17T04:54:00.000-08:00

In my company, we are starting to use TFS as source control
of DB projects.

This is a big change in the work flow and development processes.

Do you know if there an option to use SSMS
instead of VS. What I mean is to do automatically
check in and check out in SSMS like you do in VS.

For example, I want to open VS, go to source control

Or go to local solution (after getting latest version),

Open a stored procedure, it will open it in SSMS,

Then edit this SP and save it back.

On editing the SP, I want automatically to do check out.

Do you use TFS source control on databases?

If so, in which way? Editing stored procedures in VS?

Note:
There is TFS MSSCCI Provider tool.
It helps you in situation when you create a project in
SSMS, add it to the source control, then you can automatically do
check in and check out directly in the SSMS files that relate to
the project you created.

What I want, is to create a database project in the VS database
edition, add it to the source control and then use SSMS instead
of Visual Studio.

How to drop extended properties of all columns in a table

2010-12-04T04:01:00.000-08:00

When synchronizing
database schema
between DBs, you may
also need to synchronize the
extended properties.

Dropping extended properties of all columns in
a table one by one is a lot of work.

Therefore you can use the script below that will create
drop statements of extended properties for all columns
in defined table.All you have to do is to execute
the result set you got from this script.

SET NOCOUNT ONSELECT 'EXEC sp_dropextendedproperty
            @name = ''' + Extp.name + '''
             ,@level0type = ''schema''
             ,@level0name = ' + OBJECT_SCHEMA_NAME(Extp.major_id) + '
             ,@level1type = ''table''
            ,@level1name = ''' + OBJECT_NAME(Extp.major_id) + '''
           ,@level2type = ''column''
             ,@level2name = ''' + Col.name + '''
             GO'
FROM sys.columns AS Col
           LEFT JOIN sys.extended_properties AS Extp ON Col.object_id = Extp.major_id
                                                                    AND Col.column_id = Extp.minor_id
WHERE Extp.class_desc = 'OBJECT_OR_COLUMN'
              AND OBJECT_NAME(Col.object_id)='YourTableName'

Before execute this script in SSMS,
make sure you switched to 'Results to Text'

Note: In case you copied the script above and
pasted it in SSMS and the results are cut, change the
following to 1024, for example.

Thanks for reading.

So, you call yourself a DBA ..., Let`s see

2010-11-07T09:49:00.000-08:00

I got an opportunity to do job interviews when
we were looking for development DBA.

Honestly, I was in shock from some candidates.
We got many CVs where people wrote that
they have 5-6 and more years of 'hands on' experience.

Nice, I said,
there will be no
problem to find
a good DBA.
BUT,
I was wrong !!!!!!!!

People calling themselves a DBA with many years
of experience did not answer very primitive
questions related to the SQL Server.

Here some examples:

Question:
What is the difference between @@identity and scope_identity()?

Answers we got:

- @@identity returns the process id of your connection.
- @@identity is a constant and you can use it later in the code.
- @@identity is a global variable and scope_identity() is a func.
- "I don`t know, but I can search for it in the Google" (cool answer)

Question:What is the difference between varchar and nvarchar?

Answers we got:

- varchar can hold only letters and nvarchar can hold letters+numbers
- varchar is a fixed set of characters per column and nvarchar is
'flexible' to use, as much characters as needed per row.

Question:
How SQL Server stores its indexes?
What B stands for in the 'B-Tree'?

Answers:

- B is for 'Group by' clause and A is for Child (WTF !!??)
- Binary (90 % of candidates answered Binary)
Question:

How to fix index fragmentation?

Answer:

You update the statistics.

-----------------------------------------------------------

I interviewed a DBA with 10 years of expirence and he:

Did not know what the difference between Identiy and ScopeIdentiy is.
Did not know that varchar(max) hold data in row until 8k.
Did not know what the OUTPUT clause in sql 2005 is.
------------------------------------------------------------

Come on , people !
How dare you lie in your CV and come to job interview when
you know you will be tested.
I really do not get it.

I want to emphasize, these are not the only questions
that were asked. This post is not about which questions
to ask or how to do the interview.
It is just an example of answers.

Report progress from a batch/script/stored procedure

2010-10-22T08:35:00.000-07:00

There are situations when you have to update/delete
high volume of data in a table(s).For example, moving
data to another database (archiving data).

Probably you will do it by chunks, by using some loop
in order not to hurt the system(in our case production).

In such cases it will helpful to see if you batch/SP did not
stuck, like you see % of completeness when you do backup.

You can use PRINT to report progress, but the PRINT
not sends the message immediately to the client.
Anything sent to PRINT will be buffered, and not released
until the buffer is full, or the query completes.
This buffer is around 8KB in size.

We will use RAISERROR with severity 10 and NOWAIT
option.If the severity from 0 to 10, the SQL Server do
not raise an error, it returns a plan message.

RAISERROR('ProgressMessage',10,25) WITH NOWAIT

Let`s take a simple example where we want to see how many
rows were affected (it can be counter of more than one
operation) in each iteration:

DECLARE @RowsUpdated int,
@ProgressMessage varchar(50)

SET @RowsUpdated = 1
SET @ProgressMessage=''

WHILE @RowsUpdated>0
BEGIN

'you DML statement(s)'

   SET @RowsUpdated = @@ROWCOUNT
   SET @ProgressMessage= CAST(@RowsUpdated as VARCHAR(50))

   RAISERROR(@ProgressMessage,10,25) WITH NOWAIT

END

Thanks for reading

How to troubleshoot slow running query

2010-09-22T07:47:00.000-07:00

So you have a query or
stored procedure that
running slow.What are
the main steps to identify
the reason(s) and how
to change the query?

I assume you already have the query and I am not going to
talk about how to find the slow queries/stored procedures.

I am not going to talk about the CPU/Network/Memory
bottleneck and other fancy words that generally will not help
us with performance of single query or stored procedure.

What I want is to list the main steps I and my DBA team do
when we have a slow running query/SP.

1) Examine the Execution Plan:

Take a look at the query execution plan,

a. Search for Table Scan / Index Scan, these operators
    significant if you access big tables.
    Check if proper indexes already exist on the tables,
    find out why SQL Server chose not to use them,
    for example, usage of local variables, parameters sniffing,
    outdated statistics,heavily fragmented indexes can cause
   to Table/Index scan.

b. Check if you have any key lookups/bookmark lookups.
    In case of large table / big result set this one can lead to
    performance degradation. See if you can create covering index
    or include the needed columns in a non clustered index.

c. See if you have Spool operators in the execution plan.
    These operators are Blocking operators,meaning they need to
    get all the data at once and not row by row. An excellent
    deep dive into Spool operators you can find here.
    Very briefly: A spool reads the data and saves it on TempDB.
This process is used whenever the Optimizer knows that the
    density of the column is high and the intermediate result is very
    complex to calculate.

d. If you see that there is index seek operator, check if the SQL
    Server chose the right index. There are situations when Query
    Optimizer can choose "wrong" index, this can happened when
you have more than one index in the table that SQL Server
    can use to fetch the data.In such cases, check your query filters
in the Where clause.

e. Compare the Actual number of rows versus Estimated.

If you see big difference in these numbers, this is a sign
that you use wrong generated execution plan.
Check statistics, consider to use Option(recompile).

Check in the Execution Plan in XML view what
were the values of input paramters when the SP was
compiled. This can help you to undestand why
SQL Server built the execution plan you see.

f) For every operator in every query, you will see
the operator`s cost. By checking this cost, you
can easily the most expensive operators in the query.

One thing to note here:
   Even in the actual execution plan, you can only see
the estimated costs, and the percentage values represent
the estimated relative costs.
   Therefore, If there is a big difference between the
   estimated and the actual number of rows, there is a good
   chance that the relative costs are misleading.

2) Analyze the output of STATISTICS IO

By setting the SET STATISTICS IO ON,
SQL Server will display information regarding the amount of disk
activity generated by Transact-SQL statements:

Table 'ProductCostHistory'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0.

In the output you need to check the following:

Scan count - Number of index or table scans(access) performed.
Logical reads - Number of pages read from the data cache.

These counters are very handy when you compare the changes
you made in the query.

Almost in all cases the reason for slowness of a query is the high
and not really needed amount of 'pages read'/ Logical reads.
You need to check if the amount of 'page reads' is really what you
expect to be, if not , here is your main problem.

By looking at these counters you can see how many times SQL
Server accessed a table, how many pages were read.
Search for high number of Logical reads and on each change of
the query compare these values.

3) Try to rewrite the query / stored procedure

Check if you use DISTINCT / UNION,
not SARGable expressions (like != ),functions on columns
in the where clause, scalar functions,wrong Index/Join hints.
Use JOINs rather than subqueries.

How SQLCMD in SSMS can help you?

2010-09-10T13:56:00.000-07:00

SQLCMD mode allows to you combine Windows System
commands and T-SQL commands in the same batch.

First of all to enable it, click on SQLCMD icon in the toolbar:

OK, now let`s see what it can do for us:

1) You can easily save a query`s result set to a file:

:out C:\Temp\outputfile.txt
SET NOCOUNT ON
SELECT * FROM dbo.SomeTable
GO

Pay attention, the file will be saved on your local drive
and not on the SQL Server machine (Of course if you
execute this not on SQL Server box)
Tip: the column delimiter can be defined in Tools->Options:

2) You can execute script file(s) directly from the SSMS:

:r c:\scriptFile.sql

3) You can execute a DOS commands directly from the SSMS:

!!dir c:\Temp
!!type c:\temp\DemoFile.txt

!!ipconfig

and even execute a program:

!!notepad

4) You can change SQL connection:

:connect ProdServer

5) You can set variables and use them later

This is very cool, it allows us to create complex dynamic scripts:

Here simple example:

:setvar DBname Master

:setvar TableName sys.tables

USE $(DBname)
GO
SELECT *
FROM $(TableName)

Go ahead and try it !!!
What I gave as examples is just to demonstrate and introduce
to you the tool. You can get much more interesting examples
and usages here or here.

SELECT...INTO is faster than CREATE & INSERT

2010-09-04T07:10:00.000-07:00

Do you use SELECT..INTO
in your code / SPs?
Or you prefer to create
a new table and then use
INSERT INTO?

Anyway, you should be aware of the following:

The SELECT ... INTO is minimally logged operation in case
your database under the simple recovery or bulk-logged
recovery model. See more info here.

Therefore,with minimal logging, using the SELECT… INTO
statement can be more efficient than creating a table and then
populating the table with an INSERT statement.

In most of the cases we use Temp tables when we use
SELECT ... INTO.

The temp table created in the TEMPDB, right?
And as you know the recovery mode of the TEMPDB is simple.
Therefore the SQL Server will use minimal logging for our new table.

I highly recommend to you to read the Chintak`s post about this
subject. He provides real examples.