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
Tuesday, November 30, 2010
Finding execution plans of currently active session sqls
Posted by
Unknown
time:
4:30 AM
0
Reply
Key words: Performance
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.
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.
Posted by
Unknown
time:
12:24 AM
0
Reply
Key words: administration
Monday, September 20, 2010
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')
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">
...