I recently went through this problem as well with one of my databases. I have multiple databases, one for each client, and it was only happening on one database. And it seemed to start all of a sudden, so at first it was really perplexing. But I have it resolved now, and it was nothing I did in clarion, the issues were 100% backend related.
Here are some steps I used to find the issues
Look for your "most expensive queries". I used this sql script as I could filter it for a time frame to see if I had made improvements using the following steps. Change DATEADD(HOUR, -8, getdate()) to be to your liking. The -8 gives you the last 8 hours of expensive queries.
SELECT TOP 50 DB_NAME(st.dbid) DatabaseName,
SUM(qs.total_worker_time) as total_cpu_time,
SUM(qs.execution_count) as total_execution_count,
COUNT(*) as number_of_statements,
SUM(qs.total_worker_time)/SUM(qs.execution_count)/1000 AS AvgExecutionTime_seconds,
st.text as Query,
CONVERT(varchar(max), pl.query_plan)queryPlan,
GETDATE() AS TimeStamp,
qs.sql_handle
FROM
sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
cross apply sys.dm_exec_plan_attributes(qs.plan_handle) pa
cross apply sys.dm_exec_query_plan(qs.plan_handle) pl
WHERE pa.attribute = 'dbid' and
last_execution_time > DATEADD(HOUR, -8, getdate())
GROUP BY qs.plan_handle, st.text, qs.sql_handle,DB_NAME(st.dbid), CONVERT(varchar(max), pl.query_plan)
Copy a value from the query column, and paste it into a new query window. Press ctrl+L if you are in SQL Management Studio. This will show you the query plan. At the top section of the results, look for a It should also tell you how much of a performance impact you will have from adding the index. Right clicking on it should let you "Show Missing Index Details". If the query you pasted is an object like a stored procedure or trigger, run each statement separately by highlighting it and pressing ctrl+L. If you have insert\update statements, run it with the insert\update and again with only the select statement that feeds the insert\update. As these can have different query plans that may or may not show the missing indexes unless run separately.
The above really helped, but I was still having blocking issues. So another item I did was to turn of "Use page locks when accessing the index". You can find this by right clicking on your index in Management Studio, select properties and go to the options tab. For a little background on what this does... My schema uses GUIDs as my primary key which is also my clustered index (how its stored on disk). Think of this index as a Phone Book. Each page of your phone book has a list of GUIDS in alphabetical order. Rather than locking the row you are editing, the query engine will sometimes use a "Page Level" lock, making it so that no one can edit any rows on that page while the transaction is active... and well if your transaction is expensive and runs a long time, or your transaction does not close, you are stuck.
The other setting in the same tab of your index properties to pay attention to is your "Fill Factor". If you have a high transaction table, your fill factor should not be the default (usually around 80). I set mine to 60 if it is a high transaction database. This can make querying take a bit longer, but the inserts and updates go much faster. I also turn on "Pad Index". Think of this as how full you want your Page in your Phone Book to be. If you have a page that is getting filled up quickly because it is 80% fill factor, it has to create new pages and shuffle your index to make room for the new data coming in. Giving it a little more breathing room by padding and 60% fill factor makes that happen less often. This is less of an issue if your index is on a sequential number as it just adds a new page to the end of the index.
After these things were done blocking went away. Of course I also had the options you discussed earlier enabled as well.
Hopefully this helps,
Debra
Topic: MS SQL Tables Locking (Read 137390 times)