November 15, 2016 at 8:55 am
Comments posted to this topic are about the item Warm the cache with a table and its indexes
November 16, 2016 at 12:28 am
Hi Admin ,
Thanks for the script ,i am new for index .i am getting positive,zero and negative values how to validate it? please help me
Thanks
Saravanan A
November 16, 2016 at 1:04 am
Hi
That means that you are executing the queries manually.
The output of the query is of no importance.
The checksum is used since it can work on all data types.
The important bit is that it uses all indexes so the entire table including indexes are in cache after it runs.
This can help when oltp db's are seeing physical reads.
Not recommended for datawarehouse, only oltp as DW tables tend to be larger than system RAM.
Thanks for feedback.
Regards
Doran
November 16, 2016 at 2:10 am
Thanks Admin
November 22, 2016 at 5:44 am
I have an alternate suggestion to your generic script which is to manually write custom stored procedures for whatever process you are going to call. No offense but I like to control everything if I can. I use heaps extensively and custom code each statement to pull in all data pages. Also I add statements that cause every page of an index to be retrieved so their pages are all cached too, BUT, only if I need those indexes. Preheating data cache allows me to swap large percentages of cache before calling timeout sensitive procedures where apps default to 30 second timeouts for large reports. It is also very handy to preheat the data cache 15 minutes before work starts in the morning or right after a backup has put old historical data in cache and wiped out popular current data.
Thanks for the script. Perhaps I just see your script as too big of a hammer for my specific situations. And some people might not like their data being aged out of cache by you!
November 22, 2016 at 6:23 am
Bill Talada (11/22/2016)
I have an alternate suggestion to your generic script which is to manually write custom stored procedures for whatever process you are going to call. No offense but I like to control everything if I can. I would suggest adding a WHERE clause to limit output for example "where checksum() = 1" since output is a side-effect of what you're trying to accomplish and really not needed.I use heaps extensively and custom code each statement to pull in all data pages. Also I add statements that cause every page of an index to be retrieved so their pages are all cached too, but, only if I need those indexes. Preheating data cache allows me to swap large percentages of cache before calling timeout sensitive procedures where apps default to 30 second timeouts for large reports. It is also very handy to preheat the data cache 15 minutes before work starts in the morning or right after a backup has put old historical data in cache and wiped out popular current data.
Thanks for the script. Perhaps I just see your script as too big of a hammer for my specific situations. And some people might not like their data being aged out of cache by you!
For my specific case, it was necessary to load all indexes and table into memory as the delete will occur across all the indexes, and the deletes before the change were blocking, so I had to make sure that the entire delete chain was done in memory.
My use case is that the script must work if I optimise the indexing, i.e. add or drop indexes, or change the clustering etc, so no hard coding as I once found index names hard coded into SSIS packages and jobs, which caused me endless problems with implementing changes and so, I live and breath the generalise parameterised life.
To it being a hammer too large, I see scripts submitted in a too specific way, and that makes it less useful to me as I have to customise them to no end, so I do try and generalise the script in such a way that anyone can use it without having to change it.
Thank for taking the time to have a look at the script.
November 25, 2016 at 12:53 am
Hi,
Can this be used before running a series of select queries against a table?
Or is this specifically about updates/deletes?
Q
November 25, 2016 at 2:23 am
quentin.harris (11/25/2016)
Hi,Can this be used before running a series of select queries against a table?
Or is this specifically about updates/deletes?
Q
My use case was for a large number of deletes in a cleanup job.
Depending on the size table, you could do the same with selects.
An example of my case was that after query optimisation, a particular table never had any table scans.
This caused a whole host of queries against the table to almost always do physical reads when doing seeks.
In that case, I have a job which scans the table once every 4 hours.
The first metric to check is if there are physical reads against the table you are planning to do this on.
You can find this by using an event which logs the long running procs and have physical reads > 0.
I would use the proc with highest count and then link the tables in that proc with the results in
select s.name+'.'+o.name+'.'+i.name,page_io_latch_wait_count,os.*
from
sys.dm_db_index_operational_stats(db_id(),null,null,null) os
inner join sys.objects o on o.object_id=os.object_id
inner join sys.schemas s on s.schema_id = o.schema_id
inner join sys.indexes i on i.object_id = os.object_id and os.index_id = i.index_id
WHERE os.page_io_latch_wait_count>0
order by os.page_io_latch_wait_count desc
If the table is too large, then you can use the proc to ouput the queries needed to cache a particular index.
November 25, 2016 at 2:29 am
Thank you
November 28, 2016 at 8:38 am
quentin.harris (11/25/2016)
Thank you
Ditto
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply