Tuesday, November 30, 2010

Finding execution plans of currently active session sqls

Here is the script to find execution plans of currently running sqls on server. It is in xml format.

SELECT sp.spid,sp.cpu,sp.cmd, qp.query_plan from sys.sysprocesses as sp inner join sys.dm_exec_requests as er ON sp.spid=er.session_id
CROSS APPLY sys.dm_exec_query_plan(er.plan_handle)as qp
where sp.status not in ('sleeping','background') and sp.cmd not in ('DB MIRROR') order by cpu

Thursday, November 11, 2010

How to Move Sql Server 2005/2008 Mirrored Database datafile

In order to move datafiles of a mirrored database from one location to an other (requires server offline):

1-) Check datafile physical locations:

select * from master..sysaltfiles where filename like '%MYDB_Data%'

2-) Run similar alter database command to rename physical datafile names.

alter database MYDB MODIFY FILE (NAME=MYDB_Data01,FILENAME='D:\mypath\MYDB_Data01.ndf');

Following similar message will come up means change will apply when server is restarted

The file "MYDB_Data01" has been modified in the system catalog. The new path will be used the next time the database is started.

* Shutdown sql service
* copy physical files to new location
* rename old ones to some dummy name
* Start sql services

4-) If everything is fine, you can delete old files.

Note: You can not take mirrored database offline, that is why we shutdown sql services and copy datafiles while system is offline.

Monday, September 20, 2010

How To Get Partition Key Column Name


select c.name
from sys.tables t
join sys.indexes i
on(i.object_id = t.object_id
and i.index_id < 2) join sys.index_columns ic on(ic.partition_ordinal > 0
and ic.index_id = i.index_id and ic.object_id = t.object_id)
join sys.columns c
on(c.object_id = ic.object_id
and c.column_id = ic.column_id)
where t.object_id = object_id('table_name')

Wednesday, August 18, 2010

Sql Server 2005 Plan Guide

Sql Server 2005 Plan Guide

Here is how i managed to add plan guide to an sql executed by sp_executesql statement.
My sql statement is similar to this::

exec sp_executesql N'SELECT ... FROM ...WHERE col1 = @var1 AND col2 = @var2 ...',N'@var1 uniqueidentifier,@var2 int',@var1='...',@var2=...

I extracted xml of the correct execution plan and created following plan guide for this sql statement (setting static execution plan)

EXEC sp_create_plan_guide @name = N'[myplan-20100817-171848]', @stmt = N'SELECT ... FROM ...WHERE col1 = @var1 AND col2 = @var2 ...', @type = N'SQL', @module_or_batch = NULL, @params = N'@var1 uniqueidentifier,@var2 int', @hints = N'OPTION (USE PLAN ''<ShowPlanXML ... > ... </ShowPlanXML>'')'

In order to make sure that plan guide is working fine, extract actual execution plan of the query and look for following lines:

<StmtSimple StatementText="SELECT ... FROM ...WHERE col1 = @var1 AND col2 = @var2 ..." StatementId="1" StatementCompId="2" StatementType="SELECT" PlanGuideDB="mydb" PlanGuideName="myplan-20100817-171848" StatementSubTreeCost="0.00328312" StatementEstRows="1" StatementOptmLevel="FULL">
<QueryPlan DegreeOfParallelism="0" CachedPlanSize="20" CompileTime="3" CompileCPU="3" CompileMemory="376" UsePlan="1">