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