September 15, 2009 at 4:41 am
Hi all
I currently have some indexes I wish to enable the allow page lock option on, but wish to know how this may affect my system. In brief I need to find what procedures use these indexes so I can replicate the system in use.
Can I somehow look at execution plans to work out which procedures use these indexes?
Thanks
September 15, 2009 at 12:07 pm
Not sure what you are looking for, Did you run a DMV that shows you that you are need indexes and now you want to find the SP or SQL statment they belong to?
September 15, 2009 at 12:13 pm
You can get an idea of which queries use those indexes by using the execution plans available in the plan cache, yes. You should be able to run an XPath query against the plans in cache and search for the names of the indexes.
But, why do you want to control locking in that manner? What problem are you hoping to solve by taking away SQL Server's ability to do a row-level lock?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 28, 2009 at 5:10 am
Hi sorry for the slow response to this.
The indexes I am talking about currently only have row level locking enabled i.e. not page locking. My understanding is that the default and recommended setting is to allow SQL server to use both row level and page level, so it can escalate locking as it sees fit.
I am wanting to see what areas of functionality will be affected should I turn page level locking back on so I can test there will be no adverse impact for users.
Does this make sense?
September 28, 2009 at 10:15 am
my best suggestion is to run the following
select * from sys.indexes
Then get the index id that you are looking for, run profile trace and filter on index id.
You should be able to locate and SQL statement or SP that use the specific index_id in question.
hope that helps
September 28, 2009 at 10:18 am
I believe Grant is telling to run the following, which will link a SQL statement up the you xml plan, this will allow you to look at the plan but you will need to know the sql statement first.
SELECT TOP 100 SUBSTRING(b.text, (a.statement_start_offset/2) + 1,
((CASE statement_end_offset WHEN -1 THEN DATALENGTH(b.text)
ELSE a.statement_end_offset END- a.statement_start_offset)/2) + 1) AS statement_text,
c.query_plan,
Execution_count,
total_worker_time as CPU_time,
total_elapsed_time,
total_elapsed_time / execution_count avg_elapsed_time,
min_elapsed_time,
max_elapsed_time
FROM sys.dm_exec_query_stats a CROSS APPLY sys.dm_exec_sql_text (a.sql_handle) AS b
CROSS APPLY sys.dm_exec_query_plan (a.plan_handle) AS c
where SUBSTRING(b.text, (a.statement_start_offset/2) + 1,
((CASE statement_end_offset WHEN -1 THEN DATALENGTH(b.text)
ELSE a.statement_end_offset END - a.statement_start_offset)/2) + 1)
not like 'insert%'
and execution_count > 1000
-- and total_elapsed_time/Execution_count > 100
ORDER BY total_elapsed_time / execution_count DESC
September 29, 2009 at 8:47 am
>> but wish to know how this may affect my system
Then you should run a production workload against your production-mirrored test environment. You DO have one of those, RIGHT?? 😀
I would think that allowing page locks would help with concurrency issues if you are having those since it could help mitigate table-lock-escalation scenarios. It also allows index reorgs, which can also improve data availability.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply