Flush the queries from the RAM
-
GilaMonster (3/27/2008)
Regarding your slow downs...You may also want to run perfmon during one of these slow downs, see if any counters are wildly different from normal.
Check pages/sec, disk queue length, disk idle%, processor %, SQL's cache hit ratio, average lock wait time, average latch wait time.
Anything that's different from normal may indicate the potential cause of the problem.
Gila, I will like to know what are the normal values for these counters?
-
Ignacio A. Salom Rangel (3/27/2008)
GilaMonster (3/27/2008)
Regarding your slow downs...You may also want to run perfmon during one of these slow downs, see if any counters are wildly different from normal.
Check pages/sec, disk queue length, disk idle%, processor %, SQL's cache hit ratio, average lock wait time, average latch wait time.
Anything that's different from normal may indicate the potential cause of the problem.
Gila, I will like to know what are the normal values for these counters?
Check your server when everything's running fine. Do that on different days until you have a baseline of 'normal' for your environment.
Some have recommendations, but most will differ from one environment to another. You need to know what's normal for your environment so that you know when something's abnormal.
Some recommendations are as follows:
disk queue length: No more then 2/spindle for prolonged periods. Doesn't apply if the drives are in a SAN
cache hit ratio: Greater than 95%
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thank you for the information Gila.
-
Regarding fulshing the queries from the RAM, I think it is wise to use cach plan which are already in the RAM, which do not have a wide range of values so that there is no need to use RECOMPILE.
Does this query below make any difference in the cache plan.
Select column1,column2,..... from table1 inner join table2 on clause
where column1 in (@value)
and this query
Select column1,column2,..... from table1 inner join table2 on clause
where column1 = (@value).
The @value is a single value variable. Does IN and = have any difference.
This is because I have a lot of Select queries like this in Where clause.
When I query using the sys.dm_exec_cached_plans where usecounts= 1.
My understanding is that the values I recieve under text coulmns are all
cached once.i.e they are not using the cache plan in RAM and a new cache plan is written to the RAM. This is because I observe the same select queries one below another or only a single value in the where clause would have changed.Is it true? How can I make sure that for some simple select queries it should use the same cache plan in the memory instead of creating a new one?
Are there any other views where I can get the cache plan text where I can observe how many times the plan is used.
-
Ashwin,
Your example queries are definitely different and will have different plans. The IN operator is basically a shortcut for writing where column1 = x OR column1 = y OR column1 = z, etc... so many times, an IN will do an index or table scan while using = will almost always do an index seek.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Hi
what about the usecounts in sys.dm_exec_cached_plans table? How can I derive any neccessary observation from this column?
-
Usecounts tells you how many times a plan has been used since the last SQL Server restart. YOu can find out the sql executed by using this query:
Select * from sys.dm_exec_cached_plans P Cross Apply sys.dm_exec_sql_text(plan_handle) T
sys.dm_exec_sql_text is a function that gives youteh sql text and some other info.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
When I checked out the application code written previously by the ex-employee I observed that he has used lot of very big select queries about 200 columns in the application side with a single where clause.
Does this effect the cache size in the RAM or Can I replace this big Select with a stored procedure.Will it help?
-
I don't if it will reduce the size used of the plan in cache, I would guess not as it will build the same plan for the query, but I would definitely put it in a stored procedure. It will help with performance because of reduced network traffic, 1 sp name vs. ? characters in a select with 200 columns, and helps with debugging. I would also evaluate if all the columns are needed. I mean you probably are not getting the full value of indexes with that many columns involved as you probably are not hitting covering indexes.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Whenever we execute the sys.dm_exec_sql_text or other views which is very important the usecounts or size_in_bytes column. Based on which column gives me the better idea?
-
I have a Select query like
Select Column1,column2,column3 from table1 T1,table2 T2 ,table3 T3 WHERE T1.ID = T2.ID AND T1.ID = t3.ID AND T2.USERID = @VALUE AND T3.NAME LIKE '@NAME.%'
This query has taken about 10 percent of my text column in sys.dm_exec_sql_text views . This query is used a lot I want to reuse the cache plan every time this query is executed, How can I do this?
-
Your best bet will be to convert it to a stored proc and cahnge whatever's calling it.
How is that query being called? Front end app? Dynamic SQL?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
It is called from front end application. If I convert into procedure do you think that it helps a lot. This is query is used a lot in when I view the sys.dm_exec_cached_plans.
-
If you convert the query into a stored proc and change all the front end calls to reference that proc yes it will help.
I would also suggst that you change the table naming to use 2 part names (dbo.Table1 instead of just Table1), as it ensures the proc won't be recompiled because of ambiguous naming.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Does the two part naming of a table helps in single Select statement in the application side, because I have lot of Select queries in the application side which is I think not reusing the cache plan stored in the RAM. It is recompiling every time it is executed.
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply