I want to rebuild my DEV SSRS (2005), and load reports, models, etc. from
PROD. But, I want to then apply the security I had in original DEV against
the restored folders.
Does anyone know how to SCRIPT SECURITY, and then reapply over existing
folders/objects?On Jan 8, 12:23 pm, mdgraves <mdgra...@.discussions.microsoft.com>
wrote:
> I want to rebuild my DEV SSRS (2005), and load reports, models, etc. from
> PROD. But, I want to then apply the security I had in original DEV against
> the restored folders.
> Does anyone know how to SCRIPT SECURITY, and then reapply over existing
> folders/objects?
This article might be helpful; however, note the disclaimer.
http://msdn2.microsoft.com/en-us/library/ms160854.aspx
Regards,
Enrique Martinez
Sr. Software Consultant
Showing posts with label load. Show all posts
Showing posts with label load. Show all posts
Monday, March 26, 2012
Tuesday, March 20, 2012
Rebuild Index helps temporarily
I have a query that grinds to a halt after a heavy load. I have
discovered, through many variations and trials, that if I rebuild the
index of a certain table, my performance improves incredibly. Once the
load increases, the performance degrades. We rebuild our indexes daily
(overkill, but we are not 24 x 7). If you look at the table's indexes
the fragmentation is about 30%. It does NOT change after a rebuild
even though the performance does. Any ideas? (It is not a huge table
~ 5000 rows. Medium on inserts and updates)
These are the results of the SHOWCONTIG sproc
DBCC SHOWCONTIG scanning 'InventoryCount' table...
Table: 'InventoryCount' (747149707); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned........................: 28
- Extents Scanned.......................: 10
- Extent Switches.......................: 9
- Avg. Pages per Extent..................: 2.8
- Scan Density [Best Count:Actual Count]......: 40.00% [4:10]
- Logical Scan Fragmentation ..............: 25.00%
- Extent Scan Fragmentation ...............: 70.00%
- Avg. Bytes Free per Page................: 2499.0
- Avg. Page Density (full)................: 69.13%
What is the fill factor of the rebuild command you are using and how does the
query looks like?
"magkip@.hotmail.com" wrote:
> I have a query that grinds to a halt after a heavy load. I have
> discovered, through many variations and trials, that if I rebuild the
> index of a certain table, my performance improves incredibly. Once the
> load increases, the performance degrades. We rebuild our indexes daily
> (overkill, but we are not 24 x 7). If you look at the table's indexes
> the fragmentation is about 30%. It does NOT change after a rebuild
> even though the performance does. Any ideas? (It is not a huge table
> ~ 5000 rows. Medium on inserts and updates)
> These are the results of the SHOWCONTIG sproc
> DBCC SHOWCONTIG scanning 'InventoryCount' table...
> Table: 'InventoryCount' (747149707); index ID: 1, database ID: 11
> TABLE level scan performed.
> - Pages Scanned........................: 28
> - Extents Scanned.......................: 10
> - Extent Switches.......................: 9
> - Avg. Pages per Extent..................: 2.8
> - Scan Density [Best Count:Actual Count]......: 40.00% [4:10]
> - Logical Scan Fragmentation ..............: 25.00%
> - Extent Scan Fragmentation ...............: 70.00%
> - Avg. Bytes Free per Page................: 2499.0
> - Avg. Page Density (full)................: 69.13%
>
|||The query is long and complicated. We are optimizing it now. It can
run in <2 seconds after the rebuild.
I set the fill factor to 80%.
Edgardo wrote:[vbcol=seagreen]
> What is the fill factor of the rebuild command you are using and how does the
> query looks like?
> "magkip@.hotmail.com" wrote:
|||Oops. I misunderstood. I just use 'Rebuild All indexes'.
mag...@.hotmail.com wrote:[vbcol=seagreen]
> The query is long and complicated. We are optimizing it now. It can
> run in <2 seconds after the rebuild.
> I set the fill factor to 80%.
> Edgardo wrote:
|||Edgardo and Magkip,
I found this interesting and have tried to increase the fill factor on some
tables in the past. In my case however, I boosted it only slightly (from 10
percent to 15 percent). Magkip, you have boosted it to 80 percent.
Is there a method to determine just how much to change this fill amount?
For example, would a change to 80 percent mean that the table will grow by as
much as 80 percent of the original size each time it grows or is this more of
a way of forcing the disk allocation to be large enough to hold any temporary
space required during the growth? Or is it some of both?
Regards,
Jamie
"Edgardo Valdez, MCTS / MCITP" wrote:
[vbcol=seagreen]
> What is the fill factor of the rebuild command you are using and how does the
> query looks like?
> "magkip@.hotmail.com" wrote:
discovered, through many variations and trials, that if I rebuild the
index of a certain table, my performance improves incredibly. Once the
load increases, the performance degrades. We rebuild our indexes daily
(overkill, but we are not 24 x 7). If you look at the table's indexes
the fragmentation is about 30%. It does NOT change after a rebuild
even though the performance does. Any ideas? (It is not a huge table
~ 5000 rows. Medium on inserts and updates)
These are the results of the SHOWCONTIG sproc
DBCC SHOWCONTIG scanning 'InventoryCount' table...
Table: 'InventoryCount' (747149707); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned........................: 28
- Extents Scanned.......................: 10
- Extent Switches.......................: 9
- Avg. Pages per Extent..................: 2.8
- Scan Density [Best Count:Actual Count]......: 40.00% [4:10]
- Logical Scan Fragmentation ..............: 25.00%
- Extent Scan Fragmentation ...............: 70.00%
- Avg. Bytes Free per Page................: 2499.0
- Avg. Page Density (full)................: 69.13%
What is the fill factor of the rebuild command you are using and how does the
query looks like?
"magkip@.hotmail.com" wrote:
> I have a query that grinds to a halt after a heavy load. I have
> discovered, through many variations and trials, that if I rebuild the
> index of a certain table, my performance improves incredibly. Once the
> load increases, the performance degrades. We rebuild our indexes daily
> (overkill, but we are not 24 x 7). If you look at the table's indexes
> the fragmentation is about 30%. It does NOT change after a rebuild
> even though the performance does. Any ideas? (It is not a huge table
> ~ 5000 rows. Medium on inserts and updates)
> These are the results of the SHOWCONTIG sproc
> DBCC SHOWCONTIG scanning 'InventoryCount' table...
> Table: 'InventoryCount' (747149707); index ID: 1, database ID: 11
> TABLE level scan performed.
> - Pages Scanned........................: 28
> - Extents Scanned.......................: 10
> - Extent Switches.......................: 9
> - Avg. Pages per Extent..................: 2.8
> - Scan Density [Best Count:Actual Count]......: 40.00% [4:10]
> - Logical Scan Fragmentation ..............: 25.00%
> - Extent Scan Fragmentation ...............: 70.00%
> - Avg. Bytes Free per Page................: 2499.0
> - Avg. Page Density (full)................: 69.13%
>
|||The query is long and complicated. We are optimizing it now. It can
run in <2 seconds after the rebuild.
I set the fill factor to 80%.
Edgardo wrote:[vbcol=seagreen]
> What is the fill factor of the rebuild command you are using and how does the
> query looks like?
> "magkip@.hotmail.com" wrote:
|||Oops. I misunderstood. I just use 'Rebuild All indexes'.
mag...@.hotmail.com wrote:[vbcol=seagreen]
> The query is long and complicated. We are optimizing it now. It can
> run in <2 seconds after the rebuild.
> I set the fill factor to 80%.
> Edgardo wrote:
|||Edgardo and Magkip,
I found this interesting and have tried to increase the fill factor on some
tables in the past. In my case however, I boosted it only slightly (from 10
percent to 15 percent). Magkip, you have boosted it to 80 percent.
Is there a method to determine just how much to change this fill amount?
For example, would a change to 80 percent mean that the table will grow by as
much as 80 percent of the original size each time it grows or is this more of
a way of forcing the disk allocation to be large enough to hold any temporary
space required during the growth? Or is it some of both?
Regards,
Jamie
"Edgardo Valdez, MCTS / MCITP" wrote:
[vbcol=seagreen]
> What is the fill factor of the rebuild command you are using and how does the
> query looks like?
> "magkip@.hotmail.com" wrote:
Rebuild Index helps temporarily
I have a query that grinds to a halt after a heavy load. I have
discovered, through many variations and trials, that if I rebuild the
index of a certain table, my performance improves incredibly. Once the
load increases, the performance degrades. We rebuild our indexes daily
(overkill, but we are not 24 x 7). If you look at the table's indexes
the fragmentation is about 30%. It does NOT change after a rebuild
even though the performance does. Any ideas? (It is not a huge table
~ 5000 rows. Medium on inserts and updates)
These are the results of the SHOWCONTIG sproc
DBCC SHOWCONTIG scanning 'InventoryCount' table...
Table: 'InventoryCount' (747149707); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned........................: 28
- Extents Scanned.......................: 10
- Extent Switches.......................: 9
- Avg. Pages per Extent..................: 2.8
- Scan Density [Best Count:Actual Count]......: 40.00% [4:10]
- Logical Scan Fragmentation ..............: 25.00%
- Extent Scan Fragmentation ...............: 70.00%
- Avg. Bytes Free per Page................: 2499.0
- Avg. Page Density (full)................: 69.13%What is the fill factor of the rebuild command you are using and how does the
query looks like?
"magkip@.hotmail.com" wrote:
> I have a query that grinds to a halt after a heavy load. I have
> discovered, through many variations and trials, that if I rebuild the
> index of a certain table, my performance improves incredibly. Once the
> load increases, the performance degrades. We rebuild our indexes daily
> (overkill, but we are not 24 x 7). If you look at the table's indexes
> the fragmentation is about 30%. It does NOT change after a rebuild
> even though the performance does. Any ideas? (It is not a huge table
> ~ 5000 rows. Medium on inserts and updates)
> These are the results of the SHOWCONTIG sproc
> DBCC SHOWCONTIG scanning 'InventoryCount' table...
> Table: 'InventoryCount' (747149707); index ID: 1, database ID: 11
> TABLE level scan performed.
> - Pages Scanned........................: 28
> - Extents Scanned.......................: 10
> - Extent Switches.......................: 9
> - Avg. Pages per Extent..................: 2.8
> - Scan Density [Best Count:Actual Count]......: 40.00% [4:10]
> - Logical Scan Fragmentation ..............: 25.00%
> - Extent Scan Fragmentation ...............: 70.00%
> - Avg. Bytes Free per Page................: 2499.0
> - Avg. Page Density (full)................: 69.13%
>|||The query is long and complicated. We are optimizing it now. It can
run in <2 seconds after the rebuild.
I set the fill factor to 80%.
Edgardo wrote:
> What is the fill factor of the rebuild command you are using and how does the
> query looks like?
> "magkip@.hotmail.com" wrote:
> > I have a query that grinds to a halt after a heavy load. I have
> > discovered, through many variations and trials, that if I rebuild the
> > index of a certain table, my performance improves incredibly. Once the
> > load increases, the performance degrades. We rebuild our indexes daily
> > (overkill, but we are not 24 x 7). If you look at the table's indexes
> > the fragmentation is about 30%. It does NOT change after a rebuild
> > even though the performance does. Any ideas? (It is not a huge table
> > ~ 5000 rows. Medium on inserts and updates)
> >
> > These are the results of the SHOWCONTIG sproc
> > DBCC SHOWCONTIG scanning 'InventoryCount' table...
> > Table: 'InventoryCount' (747149707); index ID: 1, database ID: 11
> > TABLE level scan performed.
> > - Pages Scanned........................: 28
> > - Extents Scanned.......................: 10
> > - Extent Switches.......................: 9
> > - Avg. Pages per Extent..................: 2.8
> > - Scan Density [Best Count:Actual Count]......: 40.00% [4:10]
> > - Logical Scan Fragmentation ..............: 25.00%
> > - Extent Scan Fragmentation ...............: 70.00%
> > - Avg. Bytes Free per Page................: 2499.0
> > - Avg. Page Density (full)................: 69.13%
> >
> >|||Oops. I misunderstood. I just use 'Rebuild All indexes'.
mag...@.hotmail.com wrote:
> The query is long and complicated. We are optimizing it now. It can
> run in <2 seconds after the rebuild.
> I set the fill factor to 80%.
> Edgardo wrote:
> > What is the fill factor of the rebuild command you are using and how does the
> > query looks like?
> >
> > "magkip@.hotmail.com" wrote:
> >
> > > I have a query that grinds to a halt after a heavy load. I have
> > > discovered, through many variations and trials, that if I rebuild the
> > > index of a certain table, my performance improves incredibly. Once the
> > > load increases, the performance degrades. We rebuild our indexes daily
> > > (overkill, but we are not 24 x 7). If you look at the table's indexes
> > > the fragmentation is about 30%. It does NOT change after a rebuild
> > > even though the performance does. Any ideas? (It is not a huge table
> > > ~ 5000 rows. Medium on inserts and updates)
> > >
> > > These are the results of the SHOWCONTIG sproc
> > > DBCC SHOWCONTIG scanning 'InventoryCount' table...
> > > Table: 'InventoryCount' (747149707); index ID: 1, database ID: 11
> > > TABLE level scan performed.
> > > - Pages Scanned........................: 28
> > > - Extents Scanned.......................: 10
> > > - Extent Switches.......................: 9
> > > - Avg. Pages per Extent..................: 2.8
> > > - Scan Density [Best Count:Actual Count]......: 40.00% [4:10]
> > > - Logical Scan Fragmentation ..............: 25.00%
> > > - Extent Scan Fragmentation ...............: 70.00%
> > > - Avg. Bytes Free per Page................: 2499.0
> > > - Avg. Page Density (full)................: 69.13%
> > >
> > >|||Edgardo and Magkip,
I found this interesting and have tried to increase the fill factor on some
tables in the past. In my case however, I boosted it only slightly (from 10
percent to 15 percent). Magkip, you have boosted it to 80 percent.
Is there a method to determine just how much to change this fill amount?
For example, would a change to 80 percent mean that the table will grow by as
much as 80 percent of the original size each time it grows or is this more of
a way of forcing the disk allocation to be large enough to hold any temporary
space required during the growth? Or is it some of both?
--
Regards,
Jamie
"Edgardo Valdez, MCTS / MCITP" wrote:
> What is the fill factor of the rebuild command you are using and how does the
> query looks like?
> "magkip@.hotmail.com" wrote:
> > I have a query that grinds to a halt after a heavy load. I have
> > discovered, through many variations and trials, that if I rebuild the
> > index of a certain table, my performance improves incredibly. Once the
> > load increases, the performance degrades. We rebuild our indexes daily
> > (overkill, but we are not 24 x 7). If you look at the table's indexes
> > the fragmentation is about 30%. It does NOT change after a rebuild
> > even though the performance does. Any ideas? (It is not a huge table
> > ~ 5000 rows. Medium on inserts and updates)
> >
> > These are the results of the SHOWCONTIG sproc
> > DBCC SHOWCONTIG scanning 'InventoryCount' table...
> > Table: 'InventoryCount' (747149707); index ID: 1, database ID: 11
> > TABLE level scan performed.
> > - Pages Scanned........................: 28
> > - Extents Scanned.......................: 10
> > - Extent Switches.......................: 9
> > - Avg. Pages per Extent..................: 2.8
> > - Scan Density [Best Count:Actual Count]......: 40.00% [4:10]
> > - Logical Scan Fragmentation ..............: 25.00%
> > - Extent Scan Fragmentation ...............: 70.00%
> > - Avg. Bytes Free per Page................: 2499.0
> > - Avg. Page Density (full)................: 69.13%
> >
> >
discovered, through many variations and trials, that if I rebuild the
index of a certain table, my performance improves incredibly. Once the
load increases, the performance degrades. We rebuild our indexes daily
(overkill, but we are not 24 x 7). If you look at the table's indexes
the fragmentation is about 30%. It does NOT change after a rebuild
even though the performance does. Any ideas? (It is not a huge table
~ 5000 rows. Medium on inserts and updates)
These are the results of the SHOWCONTIG sproc
DBCC SHOWCONTIG scanning 'InventoryCount' table...
Table: 'InventoryCount' (747149707); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned........................: 28
- Extents Scanned.......................: 10
- Extent Switches.......................: 9
- Avg. Pages per Extent..................: 2.8
- Scan Density [Best Count:Actual Count]......: 40.00% [4:10]
- Logical Scan Fragmentation ..............: 25.00%
- Extent Scan Fragmentation ...............: 70.00%
- Avg. Bytes Free per Page................: 2499.0
- Avg. Page Density (full)................: 69.13%What is the fill factor of the rebuild command you are using and how does the
query looks like?
"magkip@.hotmail.com" wrote:
> I have a query that grinds to a halt after a heavy load. I have
> discovered, through many variations and trials, that if I rebuild the
> index of a certain table, my performance improves incredibly. Once the
> load increases, the performance degrades. We rebuild our indexes daily
> (overkill, but we are not 24 x 7). If you look at the table's indexes
> the fragmentation is about 30%. It does NOT change after a rebuild
> even though the performance does. Any ideas? (It is not a huge table
> ~ 5000 rows. Medium on inserts and updates)
> These are the results of the SHOWCONTIG sproc
> DBCC SHOWCONTIG scanning 'InventoryCount' table...
> Table: 'InventoryCount' (747149707); index ID: 1, database ID: 11
> TABLE level scan performed.
> - Pages Scanned........................: 28
> - Extents Scanned.......................: 10
> - Extent Switches.......................: 9
> - Avg. Pages per Extent..................: 2.8
> - Scan Density [Best Count:Actual Count]......: 40.00% [4:10]
> - Logical Scan Fragmentation ..............: 25.00%
> - Extent Scan Fragmentation ...............: 70.00%
> - Avg. Bytes Free per Page................: 2499.0
> - Avg. Page Density (full)................: 69.13%
>|||The query is long and complicated. We are optimizing it now. It can
run in <2 seconds after the rebuild.
I set the fill factor to 80%.
Edgardo wrote:
> What is the fill factor of the rebuild command you are using and how does the
> query looks like?
> "magkip@.hotmail.com" wrote:
> > I have a query that grinds to a halt after a heavy load. I have
> > discovered, through many variations and trials, that if I rebuild the
> > index of a certain table, my performance improves incredibly. Once the
> > load increases, the performance degrades. We rebuild our indexes daily
> > (overkill, but we are not 24 x 7). If you look at the table's indexes
> > the fragmentation is about 30%. It does NOT change after a rebuild
> > even though the performance does. Any ideas? (It is not a huge table
> > ~ 5000 rows. Medium on inserts and updates)
> >
> > These are the results of the SHOWCONTIG sproc
> > DBCC SHOWCONTIG scanning 'InventoryCount' table...
> > Table: 'InventoryCount' (747149707); index ID: 1, database ID: 11
> > TABLE level scan performed.
> > - Pages Scanned........................: 28
> > - Extents Scanned.......................: 10
> > - Extent Switches.......................: 9
> > - Avg. Pages per Extent..................: 2.8
> > - Scan Density [Best Count:Actual Count]......: 40.00% [4:10]
> > - Logical Scan Fragmentation ..............: 25.00%
> > - Extent Scan Fragmentation ...............: 70.00%
> > - Avg. Bytes Free per Page................: 2499.0
> > - Avg. Page Density (full)................: 69.13%
> >
> >|||Oops. I misunderstood. I just use 'Rebuild All indexes'.
mag...@.hotmail.com wrote:
> The query is long and complicated. We are optimizing it now. It can
> run in <2 seconds after the rebuild.
> I set the fill factor to 80%.
> Edgardo wrote:
> > What is the fill factor of the rebuild command you are using and how does the
> > query looks like?
> >
> > "magkip@.hotmail.com" wrote:
> >
> > > I have a query that grinds to a halt after a heavy load. I have
> > > discovered, through many variations and trials, that if I rebuild the
> > > index of a certain table, my performance improves incredibly. Once the
> > > load increases, the performance degrades. We rebuild our indexes daily
> > > (overkill, but we are not 24 x 7). If you look at the table's indexes
> > > the fragmentation is about 30%. It does NOT change after a rebuild
> > > even though the performance does. Any ideas? (It is not a huge table
> > > ~ 5000 rows. Medium on inserts and updates)
> > >
> > > These are the results of the SHOWCONTIG sproc
> > > DBCC SHOWCONTIG scanning 'InventoryCount' table...
> > > Table: 'InventoryCount' (747149707); index ID: 1, database ID: 11
> > > TABLE level scan performed.
> > > - Pages Scanned........................: 28
> > > - Extents Scanned.......................: 10
> > > - Extent Switches.......................: 9
> > > - Avg. Pages per Extent..................: 2.8
> > > - Scan Density [Best Count:Actual Count]......: 40.00% [4:10]
> > > - Logical Scan Fragmentation ..............: 25.00%
> > > - Extent Scan Fragmentation ...............: 70.00%
> > > - Avg. Bytes Free per Page................: 2499.0
> > > - Avg. Page Density (full)................: 69.13%
> > >
> > >|||Edgardo and Magkip,
I found this interesting and have tried to increase the fill factor on some
tables in the past. In my case however, I boosted it only slightly (from 10
percent to 15 percent). Magkip, you have boosted it to 80 percent.
Is there a method to determine just how much to change this fill amount?
For example, would a change to 80 percent mean that the table will grow by as
much as 80 percent of the original size each time it grows or is this more of
a way of forcing the disk allocation to be large enough to hold any temporary
space required during the growth? Or is it some of both?
--
Regards,
Jamie
"Edgardo Valdez, MCTS / MCITP" wrote:
> What is the fill factor of the rebuild command you are using and how does the
> query looks like?
> "magkip@.hotmail.com" wrote:
> > I have a query that grinds to a halt after a heavy load. I have
> > discovered, through many variations and trials, that if I rebuild the
> > index of a certain table, my performance improves incredibly. Once the
> > load increases, the performance degrades. We rebuild our indexes daily
> > (overkill, but we are not 24 x 7). If you look at the table's indexes
> > the fragmentation is about 30%. It does NOT change after a rebuild
> > even though the performance does. Any ideas? (It is not a huge table
> > ~ 5000 rows. Medium on inserts and updates)
> >
> > These are the results of the SHOWCONTIG sproc
> > DBCC SHOWCONTIG scanning 'InventoryCount' table...
> > Table: 'InventoryCount' (747149707); index ID: 1, database ID: 11
> > TABLE level scan performed.
> > - Pages Scanned........................: 28
> > - Extents Scanned.......................: 10
> > - Extent Switches.......................: 9
> > - Avg. Pages per Extent..................: 2.8
> > - Scan Density [Best Count:Actual Count]......: 40.00% [4:10]
> > - Logical Scan Fragmentation ..............: 25.00%
> > - Extent Scan Fragmentation ...............: 70.00%
> > - Avg. Bytes Free per Page................: 2499.0
> > - Avg. Page Density (full)................: 69.13%
> >
> >
Rebuild Index helps temporarily
I have a query that grinds to a halt after a heavy load. I have
discovered, through many variations and trials, that if I rebuild the
index of a certain table, my performance improves incredibly. Once the
load increases, the performance degrades. We rebuild our indexes daily
(overkill, but we are not 24 x 7). If you look at the table's indexes
the fragmentation is about 30%. It does NOT change after a rebuild
even though the performance does. Any ideas? (It is not a huge table
~ 5000 rows. Medium on inserts and updates)
These are the results of the SHOWCONTIG sproc
DBCC SHOWCONTIG scanning 'InventoryCount' table...
Table: 'InventoryCount' (747149707); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned........................: 28
- Extents Scanned.......................: 10
- Extent Switches.......................: 9
- Avg. Pages per Extent..................: 2.8
- Scan Density [Best Count:Actual Count]......: 40.00% [4:10]
- Logical Scan Fragmentation ..............: 25.00%
- Extent Scan Fragmentation ...............: 70.00%
- Avg. Bytes Free per Page................: 2499.0
- Avg. Page Density (full)................: 69.13%What is the fill factor of the rebuild command you are using and how does th
e
query looks like?
"magkip@.hotmail.com" wrote:
> I have a query that grinds to a halt after a heavy load. I have
> discovered, through many variations and trials, that if I rebuild the
> index of a certain table, my performance improves incredibly. Once the
> load increases, the performance degrades. We rebuild our indexes daily
> (overkill, but we are not 24 x 7). If you look at the table's indexes
> the fragmentation is about 30%. It does NOT change after a rebuild
> even though the performance does. Any ideas? (It is not a huge table
> ~ 5000 rows. Medium on inserts and updates)
> These are the results of the SHOWCONTIG sproc
> DBCC SHOWCONTIG scanning 'InventoryCount' table...
> Table: 'InventoryCount' (747149707); index ID: 1, database ID: 11
> TABLE level scan performed.
> - Pages Scanned........................: 28
> - Extents Scanned.......................: 10
> - Extent Switches.......................: 9
> - Avg. Pages per Extent..................: 2.8
> - Scan Density [Best Count:Actual Count]......: 40.00% [4:10]
> - Logical Scan Fragmentation ..............: 25.00%
> - Extent Scan Fragmentation ...............: 70.00%
> - Avg. Bytes Free per Page................: 2499.0
> - Avg. Page Density (full)................: 69.13%
>|||The query is long and complicated. We are optimizing it now. It can
run in <2 seconds after the rebuild.
I set the fill factor to 80%.
Edgardo wrote:[vbcol=seagreen]
> What is the fill factor of the rebuild command you are using and how does
the
> query looks like?
> "magkip@.hotmail.com" wrote:
>|||Oops. I misunderstood. I just use 'Rebuild All indexes'.
mag...@.hotmail.com wrote:[vbcol=seagreen]
> The query is long and complicated. We are optimizing it now. It can
> run in <2 seconds after the rebuild.
> I set the fill factor to 80%.
> Edgardo wrote:|||Edgardo and Magkip,
I found this interesting and have tried to increase the fill factor on some
tables in the past. In my case however, I boosted it only slightly (from 10
percent to 15 percent). Magkip, you have boosted it to 80 percent.
Is there a method to determine just how much to change this fill amount?
For example, would a change to 80 percent mean that the table will grow by a
s
much as 80 percent of the original size each time it grows or is this more o
f
a way of forcing the disk allocation to be large enough to hold any temporar
y
space required during the growth? Or is it some of both?
--
Regards,
Jamie
"Edgardo Valdez, MCTS / MCITP" wrote:
[vbcol=seagreen]
> What is the fill factor of the rebuild command you are using and how does
the
> query looks like?
> "magkip@.hotmail.com" wrote:
>
discovered, through many variations and trials, that if I rebuild the
index of a certain table, my performance improves incredibly. Once the
load increases, the performance degrades. We rebuild our indexes daily
(overkill, but we are not 24 x 7). If you look at the table's indexes
the fragmentation is about 30%. It does NOT change after a rebuild
even though the performance does. Any ideas? (It is not a huge table
~ 5000 rows. Medium on inserts and updates)
These are the results of the SHOWCONTIG sproc
DBCC SHOWCONTIG scanning 'InventoryCount' table...
Table: 'InventoryCount' (747149707); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned........................: 28
- Extents Scanned.......................: 10
- Extent Switches.......................: 9
- Avg. Pages per Extent..................: 2.8
- Scan Density [Best Count:Actual Count]......: 40.00% [4:10]
- Logical Scan Fragmentation ..............: 25.00%
- Extent Scan Fragmentation ...............: 70.00%
- Avg. Bytes Free per Page................: 2499.0
- Avg. Page Density (full)................: 69.13%What is the fill factor of the rebuild command you are using and how does th
e
query looks like?
"magkip@.hotmail.com" wrote:
> I have a query that grinds to a halt after a heavy load. I have
> discovered, through many variations and trials, that if I rebuild the
> index of a certain table, my performance improves incredibly. Once the
> load increases, the performance degrades. We rebuild our indexes daily
> (overkill, but we are not 24 x 7). If you look at the table's indexes
> the fragmentation is about 30%. It does NOT change after a rebuild
> even though the performance does. Any ideas? (It is not a huge table
> ~ 5000 rows. Medium on inserts and updates)
> These are the results of the SHOWCONTIG sproc
> DBCC SHOWCONTIG scanning 'InventoryCount' table...
> Table: 'InventoryCount' (747149707); index ID: 1, database ID: 11
> TABLE level scan performed.
> - Pages Scanned........................: 28
> - Extents Scanned.......................: 10
> - Extent Switches.......................: 9
> - Avg. Pages per Extent..................: 2.8
> - Scan Density [Best Count:Actual Count]......: 40.00% [4:10]
> - Logical Scan Fragmentation ..............: 25.00%
> - Extent Scan Fragmentation ...............: 70.00%
> - Avg. Bytes Free per Page................: 2499.0
> - Avg. Page Density (full)................: 69.13%
>|||The query is long and complicated. We are optimizing it now. It can
run in <2 seconds after the rebuild.
I set the fill factor to 80%.
Edgardo wrote:[vbcol=seagreen]
> What is the fill factor of the rebuild command you are using and how does
the
> query looks like?
> "magkip@.hotmail.com" wrote:
>|||Oops. I misunderstood. I just use 'Rebuild All indexes'.
mag...@.hotmail.com wrote:[vbcol=seagreen]
> The query is long and complicated. We are optimizing it now. It can
> run in <2 seconds after the rebuild.
> I set the fill factor to 80%.
> Edgardo wrote:|||Edgardo and Magkip,
I found this interesting and have tried to increase the fill factor on some
tables in the past. In my case however, I boosted it only slightly (from 10
percent to 15 percent). Magkip, you have boosted it to 80 percent.
Is there a method to determine just how much to change this fill amount?
For example, would a change to 80 percent mean that the table will grow by a
s
much as 80 percent of the original size each time it grows or is this more o
f
a way of forcing the disk allocation to be large enough to hold any temporar
y
space required during the growth? Or is it some of both?
--
Regards,
Jamie
"Edgardo Valdez, MCTS / MCITP" wrote:
[vbcol=seagreen]
> What is the fill factor of the rebuild command you are using and how does
the
> query looks like?
> "magkip@.hotmail.com" wrote:
>
Friday, March 9, 2012
Reasons for query execution time difference ??
Hi,
The problem faced by me is:
I tried to do a performance test on our product using Load Runner.
We captured the scripts and executed them for single user, 1 iteration.
Time taken by the entire transaction to complete at various attempts:
Attempt 1: 700 ms
Attempt 2: 17 seconds
Attempt 3: 3 seconds
Attempt 4: 18 seconds
Attempt 5: 3 seconds
Note: Before every attempt, I restored the database from a backup and
executed "DBCC DBREINDEX" and "UPDATE STATISTICS" to ensure that all the
indexes & statistics are up to date...
There is no difference in the Server anbd Client settings, environments and
the network settings...
Due to this inconsistent behaviour, I could not arrive at any kind of
conclusion about which query to tune/ which is creating the performance
bottle neck...
Interestingly, there are some queries, which always takes more time in all
the attempts and the response time for those queries are propotional to the
total time taken by the entire transaction... i.e. Query 1 takes 93 ms in
Attempt 1 and 9 sec in Attempt 2 and so on...
Moreover, whenever the system response is very poor (Attempt 2 & 4) I could
see queries related to statistics
(SELECT statman([order_id],[order_no],@.PSTATMAN) FROM (SELECT TOP
100 PERCENT [order_id],[order_no] FROM [dbo].[ord_services]
WITH(READUNCOMMITTED,SAMPLE 1.863186e+000 PERCENT) ORDER BY
[order_id],[order_no]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER_QUEUE,
MAXDOP 1)
which are fired internally by the Query Optimizer to decide on the query
plan and these queries seems to take 50% of the transaction time...
Can any one tell me about the possible reasons for the difference in the SQL
Server behaviour and the ways to solve this...
I am using SQL Server 2000 Service Pack 4
Balasubramaniam. M
Associate Consultant
SIEMENS Information Systems Ltd.
Bangalore
India
These queries look like it is updating statistics. Is Auto update statistics
turned on?... If so, try turning it off and repeating the test to see if you
get more uniform results.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bala" <Bala@.discussions.microsoft.com> wrote in message
news:A7CFC5E3-C966-44B0-904D-121045F86F04@.microsoft.com...
> Hi,
> The problem faced by me is:
> I tried to do a performance test on our product using Load Runner.
> We captured the scripts and executed them for single user, 1 iteration.
> Time taken by the entire transaction to complete at various attempts:
> Attempt 1: 700 ms
> Attempt 2: 17 seconds
> Attempt 3: 3 seconds
> Attempt 4: 18 seconds
> Attempt 5: 3 seconds
> Note: Before every attempt, I restored the database from a backup and
> executed "DBCC DBREINDEX" and "UPDATE STATISTICS" to ensure that all the
> indexes & statistics are up to date...
> There is no difference in the Server anbd Client settings, environments
and
> the network settings...
> Due to this inconsistent behaviour, I could not arrive at any kind of
> conclusion about which query to tune/ which is creating the performance
> bottle neck...
> Interestingly, there are some queries, which always takes more time in all
> the attempts and the response time for those queries are propotional to
the
> total time taken by the entire transaction... i.e. Query 1 takes 93 ms in
> Attempt 1 and 9 sec in Attempt 2 and so on...
> Moreover, whenever the system response is very poor (Attempt 2 & 4) I
could
> see queries related to statistics
> (SELECT statman([order_id],[order_no],@.PSTATMAN) FROM (SELECT TOP
> 100 PERCENT [order_id],[order_no] FROM [dbo].[ord_services]
> WITH(READUNCOMMITTED,SAMPLE 1.863186e+000 PERCENT) ORDER BY
> [order_id],[order_no]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER_QUEUE,
> MAXDOP 1)
> which are fired internally by the Query Optimizer to decide on the query
> plan and these queries seems to take 50% of the transaction time...
> Can any one tell me about the possible reasons for the difference in the
SQL
> Server behaviour and the ways to solve this...
> I am using SQL Server 2000 Service Pack 4
> --
> Balasubramaniam. M
> Associate Consultant
> SIEMENS Information Systems Ltd.
> Bangalore
> India
>
The problem faced by me is:
I tried to do a performance test on our product using Load Runner.
We captured the scripts and executed them for single user, 1 iteration.
Time taken by the entire transaction to complete at various attempts:
Attempt 1: 700 ms
Attempt 2: 17 seconds
Attempt 3: 3 seconds
Attempt 4: 18 seconds
Attempt 5: 3 seconds
Note: Before every attempt, I restored the database from a backup and
executed "DBCC DBREINDEX" and "UPDATE STATISTICS" to ensure that all the
indexes & statistics are up to date...
There is no difference in the Server anbd Client settings, environments and
the network settings...
Due to this inconsistent behaviour, I could not arrive at any kind of
conclusion about which query to tune/ which is creating the performance
bottle neck...
Interestingly, there are some queries, which always takes more time in all
the attempts and the response time for those queries are propotional to the
total time taken by the entire transaction... i.e. Query 1 takes 93 ms in
Attempt 1 and 9 sec in Attempt 2 and so on...
Moreover, whenever the system response is very poor (Attempt 2 & 4) I could
see queries related to statistics
(SELECT statman([order_id],[order_no],@.PSTATMAN) FROM (SELECT TOP
100 PERCENT [order_id],[order_no] FROM [dbo].[ord_services]
WITH(READUNCOMMITTED,SAMPLE 1.863186e+000 PERCENT) ORDER BY
[order_id],[order_no]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER_QUEUE,
MAXDOP 1)
which are fired internally by the Query Optimizer to decide on the query
plan and these queries seems to take 50% of the transaction time...
Can any one tell me about the possible reasons for the difference in the SQL
Server behaviour and the ways to solve this...
I am using SQL Server 2000 Service Pack 4
Balasubramaniam. M
Associate Consultant
SIEMENS Information Systems Ltd.
Bangalore
India
These queries look like it is updating statistics. Is Auto update statistics
turned on?... If so, try turning it off and repeating the test to see if you
get more uniform results.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bala" <Bala@.discussions.microsoft.com> wrote in message
news:A7CFC5E3-C966-44B0-904D-121045F86F04@.microsoft.com...
> Hi,
> The problem faced by me is:
> I tried to do a performance test on our product using Load Runner.
> We captured the scripts and executed them for single user, 1 iteration.
> Time taken by the entire transaction to complete at various attempts:
> Attempt 1: 700 ms
> Attempt 2: 17 seconds
> Attempt 3: 3 seconds
> Attempt 4: 18 seconds
> Attempt 5: 3 seconds
> Note: Before every attempt, I restored the database from a backup and
> executed "DBCC DBREINDEX" and "UPDATE STATISTICS" to ensure that all the
> indexes & statistics are up to date...
> There is no difference in the Server anbd Client settings, environments
and
> the network settings...
> Due to this inconsistent behaviour, I could not arrive at any kind of
> conclusion about which query to tune/ which is creating the performance
> bottle neck...
> Interestingly, there are some queries, which always takes more time in all
> the attempts and the response time for those queries are propotional to
the
> total time taken by the entire transaction... i.e. Query 1 takes 93 ms in
> Attempt 1 and 9 sec in Attempt 2 and so on...
> Moreover, whenever the system response is very poor (Attempt 2 & 4) I
could
> see queries related to statistics
> (SELECT statman([order_id],[order_no],@.PSTATMAN) FROM (SELECT TOP
> 100 PERCENT [order_id],[order_no] FROM [dbo].[ord_services]
> WITH(READUNCOMMITTED,SAMPLE 1.863186e+000 PERCENT) ORDER BY
> [order_id],[order_no]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER_QUEUE,
> MAXDOP 1)
> which are fired internally by the Query Optimizer to decide on the query
> plan and these queries seems to take 50% of the transaction time...
> Can any one tell me about the possible reasons for the difference in the
SQL
> Server behaviour and the ways to solve this...
> I am using SQL Server 2000 Service Pack 4
> --
> Balasubramaniam. M
> Associate Consultant
> SIEMENS Information Systems Ltd.
> Bangalore
> India
>
Reasons for query execution time difference ??
Hi,
The problem faced by me is:
I tried to do a performance test on our product using Load Runner.
We captured the scripts and executed them for single user, 1 iteration.
Time taken by the entire transaction to complete at various attempts:
Attempt 1: 700 ms
Attempt 2: 17 seconds
Attempt 3: 3 seconds
Attempt 4: 18 seconds
Attempt 5: 3 seconds
Note: Before every attempt, I restored the database from a backup and
executed "DBCC DBREINDEX" and "UPDATE STATISTICS" to ensure that all the
indexes & statistics are up to date...
There is no difference in the Server anbd Client settings, environments and
the network settings...
Due to this inconsistent behaviour, I could not arrive at any kind of
conclusion about which query to tune/ which is creating the performance
bottle neck...
Interestingly, there are some queries, which always takes more time in all
the attempts and the response time for those queries are propotional to the
total time taken by the entire transaction... i.e. Query 1 takes 93 ms in
Attempt 1 and 9 sec in Attempt 2 and so on...
Moreover, whenever the system response is very poor (Attempt 2 & 4) I could
see queries related to statistics
(SELECT statman([order_id],[order_no],@.PSTATMAN) FROM (SELECT TOP
100 PERCENT [order_id],[order_no] FROM [dbo].[ord_services]
WITH(READUNCOMMITTED,SAMPLE 1.863186e+000 PERCENT) ORDER BY
[order_id],[order_no]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER_QUEUE,
MAXDOP 1)
which are fired internally by the Query Optimizer to decide on the query
plan and these queries seems to take 50% of the transaction time...
Can any one tell me about the possible reasons for the difference in the SQL
Server behaviour and the ways to solve this...
I am using SQL Server 2000 Service Pack 4
--
Balasubramaniam. M
Associate Consultant
SIEMENS Information Systems Ltd.
Bangalore
IndiaThese queries look like it is updating statistics. Is Auto update statistics
turned on?... If so, try turning it off and repeating the test to see if you
get more uniform results.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bala" <Bala@.discussions.microsoft.com> wrote in message
news:A7CFC5E3-C966-44B0-904D-121045F86F04@.microsoft.com...
> Hi,
> The problem faced by me is:
> I tried to do a performance test on our product using Load Runner.
> We captured the scripts and executed them for single user, 1 iteration.
> Time taken by the entire transaction to complete at various attempts:
> Attempt 1: 700 ms
> Attempt 2: 17 seconds
> Attempt 3: 3 seconds
> Attempt 4: 18 seconds
> Attempt 5: 3 seconds
> Note: Before every attempt, I restored the database from a backup and
> executed "DBCC DBREINDEX" and "UPDATE STATISTICS" to ensure that all the
> indexes & statistics are up to date...
> There is no difference in the Server anbd Client settings, environments
and
> the network settings...
> Due to this inconsistent behaviour, I could not arrive at any kind of
> conclusion about which query to tune/ which is creating the performance
> bottle neck...
> Interestingly, there are some queries, which always takes more time in all
> the attempts and the response time for those queries are propotional to
the
> total time taken by the entire transaction... i.e. Query 1 takes 93 ms in
> Attempt 1 and 9 sec in Attempt 2 and so on...
> Moreover, whenever the system response is very poor (Attempt 2 & 4) I
could
> see queries related to statistics
> (SELECT statman([order_id],[order_no],@.PSTATMAN) FROM (SELECT TOP
> 100 PERCENT [order_id],[order_no] FROM [dbo].[ord_services]
> WITH(READUNCOMMITTED,SAMPLE 1.863186e+000 PERCENT) ORDER BY
> [order_id],[order_no]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER_QUEUE,
> MAXDOP 1)
> which are fired internally by the Query Optimizer to decide on the query
> plan and these queries seems to take 50% of the transaction time...
> Can any one tell me about the possible reasons for the difference in the
SQL
> Server behaviour and the ways to solve this...
> I am using SQL Server 2000 Service Pack 4
> --
> Balasubramaniam. M
> Associate Consultant
> SIEMENS Information Systems Ltd.
> Bangalore
> India
>
The problem faced by me is:
I tried to do a performance test on our product using Load Runner.
We captured the scripts and executed them for single user, 1 iteration.
Time taken by the entire transaction to complete at various attempts:
Attempt 1: 700 ms
Attempt 2: 17 seconds
Attempt 3: 3 seconds
Attempt 4: 18 seconds
Attempt 5: 3 seconds
Note: Before every attempt, I restored the database from a backup and
executed "DBCC DBREINDEX" and "UPDATE STATISTICS" to ensure that all the
indexes & statistics are up to date...
There is no difference in the Server anbd Client settings, environments and
the network settings...
Due to this inconsistent behaviour, I could not arrive at any kind of
conclusion about which query to tune/ which is creating the performance
bottle neck...
Interestingly, there are some queries, which always takes more time in all
the attempts and the response time for those queries are propotional to the
total time taken by the entire transaction... i.e. Query 1 takes 93 ms in
Attempt 1 and 9 sec in Attempt 2 and so on...
Moreover, whenever the system response is very poor (Attempt 2 & 4) I could
see queries related to statistics
(SELECT statman([order_id],[order_no],@.PSTATMAN) FROM (SELECT TOP
100 PERCENT [order_id],[order_no] FROM [dbo].[ord_services]
WITH(READUNCOMMITTED,SAMPLE 1.863186e+000 PERCENT) ORDER BY
[order_id],[order_no]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER_QUEUE,
MAXDOP 1)
which are fired internally by the Query Optimizer to decide on the query
plan and these queries seems to take 50% of the transaction time...
Can any one tell me about the possible reasons for the difference in the SQL
Server behaviour and the ways to solve this...
I am using SQL Server 2000 Service Pack 4
--
Balasubramaniam. M
Associate Consultant
SIEMENS Information Systems Ltd.
Bangalore
IndiaThese queries look like it is updating statistics. Is Auto update statistics
turned on?... If so, try turning it off and repeating the test to see if you
get more uniform results.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bala" <Bala@.discussions.microsoft.com> wrote in message
news:A7CFC5E3-C966-44B0-904D-121045F86F04@.microsoft.com...
> Hi,
> The problem faced by me is:
> I tried to do a performance test on our product using Load Runner.
> We captured the scripts and executed them for single user, 1 iteration.
> Time taken by the entire transaction to complete at various attempts:
> Attempt 1: 700 ms
> Attempt 2: 17 seconds
> Attempt 3: 3 seconds
> Attempt 4: 18 seconds
> Attempt 5: 3 seconds
> Note: Before every attempt, I restored the database from a backup and
> executed "DBCC DBREINDEX" and "UPDATE STATISTICS" to ensure that all the
> indexes & statistics are up to date...
> There is no difference in the Server anbd Client settings, environments
and
> the network settings...
> Due to this inconsistent behaviour, I could not arrive at any kind of
> conclusion about which query to tune/ which is creating the performance
> bottle neck...
> Interestingly, there are some queries, which always takes more time in all
> the attempts and the response time for those queries are propotional to
the
> total time taken by the entire transaction... i.e. Query 1 takes 93 ms in
> Attempt 1 and 9 sec in Attempt 2 and so on...
> Moreover, whenever the system response is very poor (Attempt 2 & 4) I
could
> see queries related to statistics
> (SELECT statman([order_id],[order_no],@.PSTATMAN) FROM (SELECT TOP
> 100 PERCENT [order_id],[order_no] FROM [dbo].[ord_services]
> WITH(READUNCOMMITTED,SAMPLE 1.863186e+000 PERCENT) ORDER BY
> [order_id],[order_no]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER_QUEUE,
> MAXDOP 1)
> which are fired internally by the Query Optimizer to decide on the query
> plan and these queries seems to take 50% of the transaction time...
> Can any one tell me about the possible reasons for the difference in the
SQL
> Server behaviour and the ways to solve this...
> I am using SQL Server 2000 Service Pack 4
> --
> Balasubramaniam. M
> Associate Consultant
> SIEMENS Information Systems Ltd.
> Bangalore
> India
>
Reasons for query execution time difference ??
Hi,
The problem faced by me is:
I tried to do a performance test on our product using Load Runner.
We captured the scripts and executed them for single user, 1 iteration.
Time taken by the entire transaction to complete at various attempts:
Attempt 1: 700 ms
Attempt 2: 17 seconds
Attempt 3: 3 seconds
Attempt 4: 18 seconds
Attempt 5: 3 seconds
Note: Before every attempt, I restored the database from a backup and
executed "DBCC DBREINDEX" and "UPDATE STATISTICS" to ensure that all the
indexes & statistics are up to date...
There is no difference in the Server anbd Client settings, environments and
the network settings...
Due to this inconsistent behaviour, I could not arrive at any kind of
conclusion about which query to tune/ which is creating the performance
bottle neck...
Interestingly, there are some queries, which always takes more time in all
the attempts and the response time for those queries are propotional to the
total time taken by the entire transaction... i.e. Query 1 takes 93 ms in
Attempt 1 and 9 sec in Attempt 2 and so on...
Moreover, whenever the system response is very poor (Attempt 2 & 4) I could
see queries related to statistics
(SELECT statman([order_id],[order_no],@.PSTATMAN) FROM (SELEC
T TOP
100 PERCENT [order_id],[order_no] FROM [dbo].[ord_services]
WITH(READUNCOMMITTED,SAMPLE 1.863186e+000 PERCENT) ORDER BY
[order_id],[order_no]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER_
QUEUE,
MAXDOP 1)
which are fired internally by the Query Optimizer to decide on the query
plan and these queries seems to take 50% of the transaction time...
Can any one tell me about the possible reasons for the difference in the SQL
Server behaviour and the ways to solve this...
I am using SQL Server 2000 Service Pack 4
Balasubramaniam. M
Associate Consultant
SIEMENS Information Systems Ltd.
Bangalore
IndiaThese queries look like it is updating statistics. Is Auto update statistics
turned on?... If so, try turning it off and repeating the test to see if you
get more uniform results.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bala" <Bala@.discussions.microsoft.com> wrote in message
news:A7CFC5E3-C966-44B0-904D-121045F86F04@.microsoft.com...
> Hi,
> The problem faced by me is:
> I tried to do a performance test on our product using Load Runner.
> We captured the scripts and executed them for single user, 1 iteration.
> Time taken by the entire transaction to complete at various attempts:
> Attempt 1: 700 ms
> Attempt 2: 17 seconds
> Attempt 3: 3 seconds
> Attempt 4: 18 seconds
> Attempt 5: 3 seconds
> Note: Before every attempt, I restored the database from a backup and
> executed "DBCC DBREINDEX" and "UPDATE STATISTICS" to ensure that all the
> indexes & statistics are up to date...
> There is no difference in the Server anbd Client settings, environments
and
> the network settings...
> Due to this inconsistent behaviour, I could not arrive at any kind of
> conclusion about which query to tune/ which is creating the performance
> bottle neck...
> Interestingly, there are some queries, which always takes more time in all
> the attempts and the response time for those queries are propotional to
the
> total time taken by the entire transaction... i.e. Query 1 takes 93 ms in
> Attempt 1 and 9 sec in Attempt 2 and so on...
> Moreover, whenever the system response is very poor (Attempt 2 & 4) I
could
> see queries related to statistics
> (SELECT statman([order_id],[order_no],@.PSTATMAN) FROM (SEL
ECT TOP
> 100 PERCENT [order_id],[order_no] FROM [dbo].[ord_services
]
> WITH(READUNCOMMITTED,SAMPLE 1.863186e+000 PERCENT) ORDER BY
> [order_id],[order_no]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZE
R_QUEUE,
> MAXDOP 1)
> which are fired internally by the Query Optimizer to decide on the query
> plan and these queries seems to take 50% of the transaction time...
> Can any one tell me about the possible reasons for the difference in the
SQL
> Server behaviour and the ways to solve this...
> I am using SQL Server 2000 Service Pack 4
> --
> Balasubramaniam. M
> Associate Consultant
> SIEMENS Information Systems Ltd.
> Bangalore
> India
>
The problem faced by me is:
I tried to do a performance test on our product using Load Runner.
We captured the scripts and executed them for single user, 1 iteration.
Time taken by the entire transaction to complete at various attempts:
Attempt 1: 700 ms
Attempt 2: 17 seconds
Attempt 3: 3 seconds
Attempt 4: 18 seconds
Attempt 5: 3 seconds
Note: Before every attempt, I restored the database from a backup and
executed "DBCC DBREINDEX" and "UPDATE STATISTICS" to ensure that all the
indexes & statistics are up to date...
There is no difference in the Server anbd Client settings, environments and
the network settings...
Due to this inconsistent behaviour, I could not arrive at any kind of
conclusion about which query to tune/ which is creating the performance
bottle neck...
Interestingly, there are some queries, which always takes more time in all
the attempts and the response time for those queries are propotional to the
total time taken by the entire transaction... i.e. Query 1 takes 93 ms in
Attempt 1 and 9 sec in Attempt 2 and so on...
Moreover, whenever the system response is very poor (Attempt 2 & 4) I could
see queries related to statistics
(SELECT statman([order_id],[order_no],@.PSTATMAN) FROM (SELEC
T TOP
100 PERCENT [order_id],[order_no] FROM [dbo].[ord_services]
WITH(READUNCOMMITTED,SAMPLE 1.863186e+000 PERCENT) ORDER BY
[order_id],[order_no]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER_
QUEUE,
MAXDOP 1)
which are fired internally by the Query Optimizer to decide on the query
plan and these queries seems to take 50% of the transaction time...
Can any one tell me about the possible reasons for the difference in the SQL
Server behaviour and the ways to solve this...
I am using SQL Server 2000 Service Pack 4
Balasubramaniam. M
Associate Consultant
SIEMENS Information Systems Ltd.
Bangalore
IndiaThese queries look like it is updating statistics. Is Auto update statistics
turned on?... If so, try turning it off and repeating the test to see if you
get more uniform results.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bala" <Bala@.discussions.microsoft.com> wrote in message
news:A7CFC5E3-C966-44B0-904D-121045F86F04@.microsoft.com...
> Hi,
> The problem faced by me is:
> I tried to do a performance test on our product using Load Runner.
> We captured the scripts and executed them for single user, 1 iteration.
> Time taken by the entire transaction to complete at various attempts:
> Attempt 1: 700 ms
> Attempt 2: 17 seconds
> Attempt 3: 3 seconds
> Attempt 4: 18 seconds
> Attempt 5: 3 seconds
> Note: Before every attempt, I restored the database from a backup and
> executed "DBCC DBREINDEX" and "UPDATE STATISTICS" to ensure that all the
> indexes & statistics are up to date...
> There is no difference in the Server anbd Client settings, environments
and
> the network settings...
> Due to this inconsistent behaviour, I could not arrive at any kind of
> conclusion about which query to tune/ which is creating the performance
> bottle neck...
> Interestingly, there are some queries, which always takes more time in all
> the attempts and the response time for those queries are propotional to
the
> total time taken by the entire transaction... i.e. Query 1 takes 93 ms in
> Attempt 1 and 9 sec in Attempt 2 and so on...
> Moreover, whenever the system response is very poor (Attempt 2 & 4) I
could
> see queries related to statistics
> (SELECT statman([order_id],[order_no],@.PSTATMAN) FROM (SEL
ECT TOP
> 100 PERCENT [order_id],[order_no] FROM [dbo].[ord_services
]
> WITH(READUNCOMMITTED,SAMPLE 1.863186e+000 PERCENT) ORDER BY
> [order_id],[order_no]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZE
R_QUEUE,
> MAXDOP 1)
> which are fired internally by the Query Optimizer to decide on the query
> plan and these queries seems to take 50% of the transaction time...
> Can any one tell me about the possible reasons for the difference in the
SQL
> Server behaviour and the ways to solve this...
> I am using SQL Server 2000 Service Pack 4
> --
> Balasubramaniam. M
> Associate Consultant
> SIEMENS Information Systems Ltd.
> Bangalore
> India
>
Wednesday, March 7, 2012
Reason 126?
What does this error mean?
Cannot load the dll xprepl.dll, or one of the dll's it references. Reason:
126(the specified module could not be found).
you can get this error is your SP 3 installation was not successful.
Have you applied this sp recently? Or a hot fix?
If so, reapply it.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Robert A. DiFrancesco" <bob.difrancesco@.comcash.com> wrote in message
news:OqD%23KYZ%23EHA.3472@.TK2MSFTNGP14.phx.gbl...
> What does this error mean?
>
> Cannot load the dll xprepl.dll, or one of the dll's it references.
Reason:
> 126(the specified module could not be found).
>
Cannot load the dll xprepl.dll, or one of the dll's it references. Reason:
126(the specified module could not be found).
you can get this error is your SP 3 installation was not successful.
Have you applied this sp recently? Or a hot fix?
If so, reapply it.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Robert A. DiFrancesco" <bob.difrancesco@.comcash.com> wrote in message
news:OqD%23KYZ%23EHA.3472@.TK2MSFTNGP14.phx.gbl...
> What does this error mean?
>
> Cannot load the dll xprepl.dll, or one of the dll's it references.
Reason:
> 126(the specified module could not be found).
>
Saturday, February 25, 2012
real time reporting + OLTP dbs
How does one implement real time reporting off of data from the OLTP
systems. Right now we run nightly extracts and load into OLAP DBs to
minimise locking,etc while the extracts are being run. However our customers
now want to see real time data as soon as possible and we cannot give them
access to the live OLTP systems. Are there any ways to implement this
efficiently ?Have you tried replication? It should give you more frequent updates.
You might want to consider using read uncommitted query because replication
also needs to make changes to your OLAP database to keep data up to date.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:upTfU1d0EHA.3584@.TK2MSFTNGP11.phx.gbl...
> How does one implement real time reporting off of data from the OLTP
> systems. Right now we run nightly extracts and load into OLAP DBs to
> minimise locking,etc while the extracts are being run. However our
> customers
> now want to see real time data as soon as possible and we cannot give them
> access to the live OLTP systems. Are there any ways to implement this
> efficiently ?
>|||we thought about replication but cant really justify replicating each of our
OLTP dbs to serve the reporting needs besides the administration needed to
support it ,etc..
"wei xiao [MSFT]" <weix@.online.microsoft.com> wrote in message
news:Oc0vFSf0EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Have you tried replication? It should give you more frequent updates.
> You might want to consider using read uncommitted query because
replication
> also needs to make changes to your OLAP database to keep data up to date.
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://weblogs.asp.net/weix
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:upTfU1d0EHA.3584@.TK2MSFTNGP11.phx.gbl...
> > How does one implement real time reporting off of data from the OLTP
> > systems. Right now we run nightly extracts and load into OLAP DBs to
> > minimise locking,etc while the extracts are being run. However our
> > customers
> > now want to see real time data as soon as possible and we cannot give
them
> > access to the live OLTP systems. Are there any ways to implement this
> > efficiently ?
> >
> >
>
systems. Right now we run nightly extracts and load into OLAP DBs to
minimise locking,etc while the extracts are being run. However our customers
now want to see real time data as soon as possible and we cannot give them
access to the live OLTP systems. Are there any ways to implement this
efficiently ?Have you tried replication? It should give you more frequent updates.
You might want to consider using read uncommitted query because replication
also needs to make changes to your OLAP database to keep data up to date.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:upTfU1d0EHA.3584@.TK2MSFTNGP11.phx.gbl...
> How does one implement real time reporting off of data from the OLTP
> systems. Right now we run nightly extracts and load into OLAP DBs to
> minimise locking,etc while the extracts are being run. However our
> customers
> now want to see real time data as soon as possible and we cannot give them
> access to the live OLTP systems. Are there any ways to implement this
> efficiently ?
>|||we thought about replication but cant really justify replicating each of our
OLTP dbs to serve the reporting needs besides the administration needed to
support it ,etc..
"wei xiao [MSFT]" <weix@.online.microsoft.com> wrote in message
news:Oc0vFSf0EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Have you tried replication? It should give you more frequent updates.
> You might want to consider using read uncommitted query because
replication
> also needs to make changes to your OLAP database to keep data up to date.
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://weblogs.asp.net/weix
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:upTfU1d0EHA.3584@.TK2MSFTNGP11.phx.gbl...
> > How does one implement real time reporting off of data from the OLTP
> > systems. Right now we run nightly extracts and load into OLAP DBs to
> > minimise locking,etc while the extracts are being run. However our
> > customers
> > now want to see real time data as soon as possible and we cannot give
them
> > access to the live OLTP systems. Are there any ways to implement this
> > efficiently ?
> >
> >
>
real time reporting + OLTP dbs
How does one implement real time reporting off of data from the OLTP
systems. Right now we run nightly extracts and load into OLAP DBs to
minimise locking,etc while the extracts are being run. However our customers
now want to see real time data as soon as possible and we cannot give them
access to the live OLTP systems. Are there any ways to implement this
efficiently ?
Have you tried replication? It should give you more frequent updates.
You might want to consider using read uncommitted query because replication
also needs to make changes to your OLAP database to keep data up to date.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:upTfU1d0EHA.3584@.TK2MSFTNGP11.phx.gbl...
> How does one implement real time reporting off of data from the OLTP
> systems. Right now we run nightly extracts and load into OLAP DBs to
> minimise locking,etc while the extracts are being run. However our
> customers
> now want to see real time data as soon as possible and we cannot give them
> access to the live OLTP systems. Are there any ways to implement this
> efficiently ?
>
|||we thought about replication but cant really justify replicating each of our
OLTP dbs to serve the reporting needs besides the administration needed to
support it ,etc..
"wei xiao [MSFT]" <weix@.online.microsoft.com> wrote in message
news:Oc0vFSf0EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Have you tried replication? It should give you more frequent updates.
> You might want to consider using read uncommitted query because
replication
> also needs to make changes to your OLAP database to keep data up to date.
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://weblogs.asp.net/weix
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:upTfU1d0EHA.3584@.TK2MSFTNGP11.phx.gbl...
them
>
systems. Right now we run nightly extracts and load into OLAP DBs to
minimise locking,etc while the extracts are being run. However our customers
now want to see real time data as soon as possible and we cannot give them
access to the live OLTP systems. Are there any ways to implement this
efficiently ?
Have you tried replication? It should give you more frequent updates.
You might want to consider using read uncommitted query because replication
also needs to make changes to your OLAP database to keep data up to date.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:upTfU1d0EHA.3584@.TK2MSFTNGP11.phx.gbl...
> How does one implement real time reporting off of data from the OLTP
> systems. Right now we run nightly extracts and load into OLAP DBs to
> minimise locking,etc while the extracts are being run. However our
> customers
> now want to see real time data as soon as possible and we cannot give them
> access to the live OLTP systems. Are there any ways to implement this
> efficiently ?
>
|||we thought about replication but cant really justify replicating each of our
OLTP dbs to serve the reporting needs besides the administration needed to
support it ,etc..
"wei xiao [MSFT]" <weix@.online.microsoft.com> wrote in message
news:Oc0vFSf0EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Have you tried replication? It should give you more frequent updates.
> You might want to consider using read uncommitted query because
replication
> also needs to make changes to your OLAP database to keep data up to date.
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://weblogs.asp.net/weix
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:upTfU1d0EHA.3584@.TK2MSFTNGP11.phx.gbl...
them
>
real time reporting + OLTP dbs
How does one implement real time reporting off of data from the OLTP
systems. Right now we run nightly extracts and load into OLAP DBs to
minimise locking,etc while the extracts are being run. However our customers
now want to see real time data as soon as possible and we cannot give them
access to the live OLTP systems. Are there any ways to implement this
efficiently ?Have you tried replication? It should give you more frequent updates.
You might want to consider using read uncommitted query because replication
also needs to make changes to your OLAP database to keep data up to date.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:upTfU1d0EHA.3584@.TK2MSFTNGP11.phx.gbl...
> How does one implement real time reporting off of data from the OLTP
> systems. Right now we run nightly extracts and load into OLAP DBs to
> minimise locking,etc while the extracts are being run. However our
> customers
> now want to see real time data as soon as possible and we cannot give them
> access to the live OLTP systems. Are there any ways to implement this
> efficiently ?
>|||we thought about replication but cant really justify replicating each of our
OLTP dbs to serve the reporting needs besides the administration needed to
support it ,etc..
"wei xiao [MSFT]" <weix@.online.microsoft.com> wrote in message
news:Oc0vFSf0EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Have you tried replication? It should give you more frequent updates.
> You might want to consider using read uncommitted query because
replication
> also needs to make changes to your OLAP database to keep data up to date.
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://weblogs.asp.net/weix
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:upTfU1d0EHA.3584@.TK2MSFTNGP11.phx.gbl...
them[vbcol=seagreen]
>
systems. Right now we run nightly extracts and load into OLAP DBs to
minimise locking,etc while the extracts are being run. However our customers
now want to see real time data as soon as possible and we cannot give them
access to the live OLTP systems. Are there any ways to implement this
efficiently ?Have you tried replication? It should give you more frequent updates.
You might want to consider using read uncommitted query because replication
also needs to make changes to your OLAP database to keep data up to date.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:upTfU1d0EHA.3584@.TK2MSFTNGP11.phx.gbl...
> How does one implement real time reporting off of data from the OLTP
> systems. Right now we run nightly extracts and load into OLAP DBs to
> minimise locking,etc while the extracts are being run. However our
> customers
> now want to see real time data as soon as possible and we cannot give them
> access to the live OLTP systems. Are there any ways to implement this
> efficiently ?
>|||we thought about replication but cant really justify replicating each of our
OLTP dbs to serve the reporting needs besides the administration needed to
support it ,etc..
"wei xiao [MSFT]" <weix@.online.microsoft.com> wrote in message
news:Oc0vFSf0EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Have you tried replication? It should give you more frequent updates.
> You might want to consider using read uncommitted query because
replication
> also needs to make changes to your OLAP database to keep data up to date.
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://weblogs.asp.net/weix
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:upTfU1d0EHA.3584@.TK2MSFTNGP11.phx.gbl...
them[vbcol=seagreen]
>
Subscribe to:
Posts (Atom)