October 17, 2011 at 9:22 pm
When we have a Page Split issue,does it directly affect on the run time of Insert statement(that has caused Page split).I mean In the SQL profiler will it increase the Duration(run time) of that Insert statement or we will face with a delay between insert statement and next SQL statement
What number in Page split/Sec in Performance monitor should we consider as an existence of Page Split.
Thanks
October 17, 2011 at 10:09 pm
Aspet Golestanian Namagerdi (10/17/2011)
When we have a Page Split issue,does it directly affect on the run time of Insert statement(that has caused Page split).I mean In the SQL profiler will it increase the Duration(run time) of that Insert statement or we will face with a delay between insert statement and next SQL statementWhat number in Page split/Sec in Performance monitor should we consider as an existence of Page Split.
Thanks
Page splits add delay to the query that does the insert.
The number of Page split/sec to be concerned about really depends on the total number of inserts.
How are you managing fragmentation in your indexes?
October 17, 2011 at 10:35 pm
On weekend we have scheduled job that defragment all the Indexes.
For a table which has 40,000,000 rows ,we are trying to insert 3000 rows in a loop.
October 17, 2011 at 10:57 pm
What number in Page split/Sec in Performance monitor should we consider as an existence of Page Split.
Thanks
Page split will cause performance issues for both insert and select. SQL has to traverse many number of pages to fetch your data if there are many page splits.
Page Splits/sec < 20 per 100 Batch Requests/Sec
Description:
Monitors the number of page splits per second which occur due to overflowing index pages and should be as low as possible. To avoid page splits, review table and index design to reduce non-sequential inserts or implement fillfactor and pad_index to leave more empty space per page. NOTE: A high value for this counter is not bad in situations where many new pages are being created, since it includes
new page allocations.
October 17, 2011 at 11:13 pm
Is Batch Requests/Sec another counter in Performance monitor?where can I get that?
In may case the Insert itself is very fast,just after insert sometimes there is a delay from 10-400ms.
October 17, 2011 at 11:43 pm
Aspet Golestanian Namagerdi (10/17/2011)
Is Batch Requests/Sec another counter in Performance monitor?where can I get that?
Object: SQLServer:SQL Statistics
Counter: Batch Requests/Secv
Description:
Number of batch requests received per second,and is a good general indicator for the activity
level of the SQL Server. This counter is highlydependent on the hardware and quality of code
running on the server. The more powerful the hardware, the higher this number can be, even
on poorly coded applications. A value of 1000 batch requests/sec is easily attainable though a
typical 100Mbs NIC can only handle about 3000 batch requests/sec.Many other counter thresholds
depend upon batch requests/sec while, in some cases, a low (or high) number does not
point to poor processing power. You should frequently use this counter in combination with
other counters, such as processor utilization or user connections.In version 2000, “Transactions/
sec” was the counter most often used to measure overall activity, while versions 2005 and later use
“Batch Requests/sec”. Versions 2005 prior to SP2, measure this counter differently and may lead to
some misunderstandings.
Aspet Golestanian Namagerdi (10/17/2011)
In may case the Insert itself is very fast,just after insert sometimes there is a delay from 10-400ms.
How do you evaluate this please?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply