May 5, 2010 at 7:04 am
Sometimes the hot pages may be the performance bottleneck?
how to figure out the hot pages ?
Should we set the fill factor to 90 or other lower?
---------------------------------------
Thorn Bird...
May 5, 2010 at 7:51 am
First you need to identify if you are seeing blocking as the primary source of your performance problems. The best way to do this is to monitor the wait states of the server to see what the processes are waiting on. Take a look at sys.dm_os_wait_stats and sys.dm_exec_requests to see things being blocked and what they're waiting on.
Once you identify that, drilling down to see the specific issue is pretty easy.
It's not that a page is going to be a problem, it's that an index or a table will have problems due to the distribution of data which focuses updates on a single page or set of pages (which usually change over time). Once you identify the index or table, and the query accessing the index or table, you need to determine if it's a structural issue (a bad or missing index) or a code issue (tsql causing problems).
"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
May 5, 2010 at 8:17 am
Grant Fritchey (5/5/2010)
First you need to identify if you are seeing blocking as the primary source of your performance problems. The best way to do this is to monitor the wait states of the server to see what the processes are waiting on. Take a look at sys.dm_os_wait_stats and sys.dm_exec_requests to see things being blocked and what they're waiting on.Once you identify that, drilling down to see the specific issue is pretty easy.
It's not that a page is going to be a problem, it's that an index or a table will have problems due to the distribution of data which focuses updates on a single page or set of pages (which usually change over time). Once you identify the index or table, and the query accessing the index or table, you need to determine if it's a structural issue (a bad or missing index) or a code issue (tsql causing problems).
Very appreciate your answer.:-)
In Oracle ,the hot spot happens when there are too many session require the same page, and the reason is that every session need a TX lock on that page( also called block).
In oracle ,we can increase the pctfree parameter to avoid the hot spot.
So what the principle of the hot spot in the sql server? And the fill factor is the same as the pctfree in oracle ?:-D
---------------------------------------
Thorn Bird...
May 5, 2010 at 8:27 am
kinzent (5/5/2010)
Grant Fritchey (5/5/2010)
First you need to identify if you are seeing blocking as the primary source of your performance problems. The best way to do this is to monitor the wait states of the server to see what the processes are waiting on. Take a look at sys.dm_os_wait_stats and sys.dm_exec_requests to see things being blocked and what they're waiting on.Once you identify that, drilling down to see the specific issue is pretty easy.
It's not that a page is going to be a problem, it's that an index or a table will have problems due to the distribution of data which focuses updates on a single page or set of pages (which usually change over time). Once you identify the index or table, and the query accessing the index or table, you need to determine if it's a structural issue (a bad or missing index) or a code issue (tsql causing problems).
Very appreciate your answer.:-)
In Oracle ,the hot spot happens when there are too many session require the same page, and the reason is that every session need a TX lock on that page( also called block).
In oracle ,we can increase the pctfree parameter to avoid the hot spot.
So what the principle of the hot spot in the sql server? And the fill factor is the same as the pctfree in oracle ?:-D
Yeah, the concept is the same. And yes, fill factor can help to alleviate it somewhat.
"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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply