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

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.

3-)
* 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.

How To Get Partition Key Column Name

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=125819

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

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