I've got a monstrous beast of a report that has about 15 datasets. I've
figured the only way i can imporve performance is if I could somehow put some
of the parameterized data it pulls into a table that the other data queries
could reuse. (Is it possible to use a temp table? I would think not if the
queries are all in different sprocs). The only way I can see this happening
(from searching the newsgroups) is by making sure that everything is done as
a single transaction in the data source, but I can't find that property
anywhere?! Where do I set that property on the datasource, to do "single
transaction" pulls?
thanks a million if you can help me.
isamuMicrosoft MVPs are you there? I'd really really appreciate it if I could get
some help here. Thanks!
"isamu" wrote:
> I've got a monstrous beast of a report that has about 15 datasets. I've
> figured the only way i can imporve performance is if I could somehow put some
> of the parameterized data it pulls into a table that the other data queries
> could reuse. (Is it possible to use a temp table? I would think not if the
> queries are all in different sprocs). The only way I can see this happening
> (from searching the newsgroups) is by making sure that everything is done as
> a single transaction in the data source, but I can't find that property
> anywhere?! Where do I set that property on the datasource, to do "single
> transaction" pulls?
> thanks a million if you can help me.
> isamu|||bump.
"isamu" wrote:
> Microsoft MVPs are you there? I'd really really appreciate it if I could get
> some help here. Thanks!
> "isamu" wrote:
> > I've got a monstrous beast of a report that has about 15 datasets. I've
> > figured the only way i can imporve performance is if I could somehow put some
> > of the parameterized data it pulls into a table that the other data queries
> > could reuse. (Is it possible to use a temp table? I would think not if the
> > queries are all in different sprocs). The only way I can see this happening
> > (from searching the newsgroups) is by making sure that everything is done as
> > a single transaction in the data source, but I can't find that property
> > anywhere?! Where do I set that property on the datasource, to do "single
> > transaction" pulls?
> >
> > thanks a million if you can help me.
> >
> > isamu|||At the bottom of the Data Source dialog in report designer you will find a
checkbox "Use Single Transaction". This indicates the data sets that use
this data source should be executed in a single transaction. So, if all your
datasets use the same data source and you apply a single transaction - you
are almost there.
Although we don't guarantee a certain execution order across datasets of the
same data source (and it may change in future), you can assume at least for
RS2000 that the order of execution is the order of the <DataSet> elements as
defined in the RDL XML. Just try it.
Hope this helps,
Robert
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"isamu" <isamu@.discussions.microsoft.com> wrote in message
news:A4DF278F-A80A-42C0-A6D8-08B41D81F60F@.microsoft.com...
> bump.
> "isamu" wrote:
>> Microsoft MVPs are you there? I'd really really appreciate it if I could
>> get
>> some help here. Thanks!
>> "isamu" wrote:
>> > I've got a monstrous beast of a report that has about 15 datasets.
>> > I've
>> > figured the only way i can imporve performance is if I could somehow
>> > put some
>> > of the parameterized data it pulls into a table that the other data
>> > queries
>> > could reuse. (Is it possible to use a temp table? I would think not if
>> > the
>> > queries are all in different sprocs). The only way I can see this
>> > happening
>> > (from searching the newsgroups) is by making sure that everything is
>> > done as
>> > a single transaction in the data source, but I can't find that property
>> > anywhere?! Where do I set that property on the datasource, to do
>> > "single
>> > transaction" pulls?
>> >
>> > thanks a million if you can help me.
>> >
>> > isamu
Showing posts with label performance. Show all posts
Showing posts with label performance. Show all posts
Sunday, March 11, 2012
Sunday, February 19, 2012
"Identical" database, huge performance difference
I have a database that has big performance issue. I backup (complete
backup) the database and restore it with a new name within the same
instance, the performance gets back to normal. The size of both database
is 1,900 MB and space available of both is 1,100 MB.
Any idea about what makes the difference?so you're saying that you can:
* start with DB1
* back up DB1 and restore it as DB2
* you could then run the same exact query on DB1 and DB2. It would be fast
on DB2 and slow on DB1?
* If so... what steps do you need to do to then make DB2 slow? Or does
performance stay good forever?
Have you looked at SQL Profiler?
What are the exact performance problems you're seeing?
Are you restoring DB2 to the same disk arrays as DB1?
Is it possible that the physical files associated with DB1 have a serious
fragmentation problem?
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"qluo" <jluost1@.yahoo.com> wrote in message
news:40042824.2030705@.yahoo.com...
> I have a database that has big performance issue. I backup (complete
> backup) the database and restore it with a new name within the same
> instance, the performance gets back to normal. The size of both database
> is 1,900 MB and space available of both is 1,100 MB.
> Any idea about what makes the difference?
>|||Brian,
You are correct for the procedures I performed.
The performance problem is, the Web page with DB1 as the backend
database takes too long to load.
The query I used in Query Analyzer on both DB1 and DB2 is:
--
select count(*) from table1
--
It returns 72,000 on both database.
For DB1, it takes 220 ms consistently; for DB2, it takes 36ms consistently.
I have not looked at the fragmentation problem yet. But the data file
and log file for both databases are in the same physical location.
Brian Moran wrote:
> so you're saying that you can:
> * start with DB1
> * back up DB1 and restore it as DB2
> * you could then run the same exact query on DB1 and DB2. It would be fast
> on DB2 and slow on DB1?
> * If so... what steps do you need to do to then make DB2 slow? Or does
> performance stay good forever?
> Have you looked at SQL Profiler?
> What are the exact performance problems you're seeing?
> Are you restoring DB2 to the same disk arrays as DB1?
> Is it possible that the physical files associated with DB1 have a serious
> fragmentation problem?
>|||is DB1 a live database? ie, are people connecting to DB1
and making updates to table1?
if so, then the reason DB2 is faster for the same query is
that it knows no one has updated table1 on DB2, hence it
is safe to execute as select count(*) from table1 (NOLOCK)
while DB1 must row lock if there were recent
inserts/upd/del to table1
try
select count(*) from table1 (NOLOCK)
on both
-joe
>--Original Message--
>Brian,
>You are correct for the procedures I performed.
>The performance problem is, the Web page with DB1 as the
backend
>database takes too long to load.
>The query I used in Query Analyzer on both DB1 and DB2 is:
>--
>select count(*) from table1
>--
>It returns 72,000 on both database.
>For DB1, it takes 220 ms consistently; for DB2, it takes
36ms consistently.
>I have not looked at the fragmentation problem yet. But
the data file
>and log file for both databases are in the same physical
location.
>
>Brian Moran wrote:
>> so you're saying that you can:
>> * start with DB1
>> * back up DB1 and restore it as DB2
>> * you could then run the same exact query on DB1 and
DB2. It would be fast
>> on DB2 and slow on DB1?
>> * If so... what steps do you need to do to then make
DB2 slow? Or does
>> performance stay good forever?
>> Have you looked at SQL Profiler?
>> What are the exact performance problems you're seeing?
>> Are you restoring DB2 to the same disk arrays as DB1?
>> Is it possible that the physical files associated with
DB1 have a serious
>> fragmentation problem?
>.
>|||Your point is exactly right. I ran the query with (NOLOCK) on both DB1
and DB2 and they are taking the same amount of time, and dB1 is quicker
now than before.
So, what do I need to do on DB1 to achieve the same performance without
using (NOLOCK)?
DB1 is not actually busy and it is used by Web applications.
Thank you.
joe chang wrote:
> is DB1 a live database? ie, are people connecting to DB1
> and making updates to table1?
> if so, then the reason DB2 is faster for the same query is
> that it knows no one has updated table1 on DB2, hence it
> is safe to execute as select count(*) from table1 (NOLOCK)
> while DB1 must row lock if there were recent
> inserts/upd/del to table1
> try
> select count(*) from table1 (NOLOCK)
> on both
> -joe
>>--Original Message--
>>Brian,
>>You are correct for the procedures I performed.
>>The performance problem is, the Web page with DB1 as the
> backend
>>database takes too long to load.
>>The query I used in Query Analyzer on both DB1 and DB2 is:
>>--
>>select count(*) from table1
>>--
>>It returns 72,000 on both database.
>>For DB1, it takes 220 ms consistently; for DB2, it takes
> 36ms consistently.
>>I have not looked at the fragmentation problem yet. But
> the data file
>>and log file for both databases are in the same physical
> location.
>>Brian Moran wrote:
>>so you're saying that you can:
>>* start with DB1
>>* back up DB1 and restore it as DB2
>>* you could then run the same exact query on DB1 and
> DB2. It would be fast
>>on DB2 and slow on DB1?
>>* If so... what steps do you need to do to then make
> DB2 slow? Or does
>>performance stay good forever?
>>Have you looked at SQL Profiler?
>>What are the exact performance problems you're seeing?
>>Are you restoring DB2 to the same disk arrays as DB1?
>>Is it possible that the physical files associated with
> DB1 have a serious
>>fragmentation problem?
>>
>>.
>|||You could set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED for your
connection. There should be a trx isolation level property for your
database connectivity object. Not that this setting will be effective for
all queries passing through the connection.
--
Regards
Ray Mond|||I am reluctant to change the default (Read Committed).
My database is not busy at all. It is used by Web app and every
connection should come and go almost immediately. There shouldn't be a
concurrent(locking) issue like this.
Do you know what else I can do to minimize the locking so that I don't
have to change TRANSACTION ISOLATION LEVEL?
Thank you.
Ray Mond wrote:
> You could set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED for your
> connection. There should be a trx isolation level property for your
> database connectivity object. Not that this setting will be effective for
> all queries passing through the connection.
Your point is exactly right. I ran the query with (NOLOCK) on both DB1
and DB2 and they are taking the same amount of time, and dB1 is quicker
now than before.
So, what do I need to do on DB1 to achieve the same performance without
using (NOLOCK)?
DB1 is not actually busy and it is used by Web applications.
Thank you.
joe chang wrote:
is DB1 a live database? ie, are people connecting to DB1 and making
updates to table1?
if so, then the reason DB2 is faster for the same query is that it knows
no one has updated table1 on DB2, hence it is safe to execute as select
count(*) from table1 (NOLOCK)
while DB1 must row lock if there were recent inserts/upd/del to table1
try
select count(*) from table1 (NOLOCK)
on both
-joe
--Original Message--
Brian,
You are correct for the procedures I performed.
The performance problem is, the Web page with DB1 as the
backend
database takes too long to load.
The query I used in Query Analyzer on both DB1 and DB2 is:
--
select count(*) from table1
--
It returns 72,000 on both database.
For DB1, it takes 220 ms consistently; for DB2, it takes
36ms consistently.
I have not looked at the fragmentation problem yet. But
the data file
and log file for both databases are in the same physical
location.
Brian Moran wrote:
so you're saying that you can:
* start with DB1
* back up DB1 and restore it as DB2
* you could then run the same exact query on DB1 and
DB2. It would be fast
on DB2 and slow on DB1?
* If so... what steps do you need to do to then make
DB2 slow? Or does
performance stay good forever?
Have you looked at SQL Profiler?
What are the exact performance problems you're seeing?
Are you restoring DB2 to the same disk arrays as DB1?
Is it possible that the physical files associated with
DB1 have a serious
fragmentation problem?
.
>|||I think the only option left here is to use that suggested by Joe Young i.e.
use the (NOLOCK) hint in the queries where you do not care about open
transactions.
--
Regards
Ray Mond
"qluo" <jluost1@.yahoo.com> wrote in message
news:400554D8.8080900@.yahoo.com...
> I am reluctant to change the default (Read Committed).
> My database is not busy at all. It is used by Web app and every
> connection should come and go almost immediately. There shouldn't be a
> concurrent(locking) issue like this.
> Do you know what else I can do to minimize the locking so that I don't
> have to change TRANSACTION ISOLATION LEVEL?
> Thank you.
> Ray Mond wrote:
> > You could set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED for your
> > connection. There should be a trx isolation level property for your
> > database connectivity object. Not that this setting will be effective
for
> > all queries passing through the connection.
> Your point is exactly right. I ran the query with (NOLOCK) on both DB1
> and DB2 and they are taking the same amount of time, and dB1 is quicker
> now than before.
> So, what do I need to do on DB1 to achieve the same performance without
> using (NOLOCK)?
> DB1 is not actually busy and it is used by Web applications.
> Thank you.
>
> joe chang wrote:
> is DB1 a live database? ie, are people connecting to DB1 and making
> updates to table1?
> if so, then the reason DB2 is faster for the same query is that it knows
> no one has updated table1 on DB2, hence it is safe to execute as select
> count(*) from table1 (NOLOCK)
> while DB1 must row lock if there were recent inserts/upd/del to table1
> try
> select count(*) from table1 (NOLOCK)
> on both
> -joe
> --Original Message--
> Brian,
> You are correct for the procedures I performed.
> The performance problem is, the Web page with DB1 as the
> backend
> database takes too long to load.
> The query I used in Query Analyzer on both DB1 and DB2 is:
> --
> select count(*) from table1
> --
> It returns 72,000 on both database.
> For DB1, it takes 220 ms consistently; for DB2, it takes
> 36ms consistently.
> I have not looked at the fragmentation problem yet. But
> the data file
> and log file for both databases are in the same physical
> location.
>
> Brian Moran wrote:
> so you're saying that you can:
> * start with DB1
> * back up DB1 and restore it as DB2
> * you could then run the same exact query on DB1 and
> DB2. It would be fast
> on DB2 and slow on DB1?
> * If so... what steps do you need to do to then make
> DB2 slow? Or does
> performance stay good forever?
> Have you looked at SQL Profiler?
> What are the exact performance problems you're seeing?
> Are you restoring DB2 to the same disk arrays as DB1?
> Is it possible that the physical files associated with
> DB1 have a serious
> fragmentation problem?
>
> .
>
> >
>|||I did "Set transaction isolation level read uncommitted" on the database
server, but it made no difference. I think the OLEDB used by PHP Web app
is using "Set transaction isolation level read committed". So no
matter what I have set on database server doesn't matter.
I can use "NOLOCK" hint for each sql statement in the application. But
there are too many of them and I am reluctant to do so.
Again, I backed up DB1 and restore it with the new name DB2. I don't
need to use "NOLOCK" hint on DB2. But for DB1, I have to use the hint to
gain the same performance.
When I check the Process/Locks in SQL Enterprise Manager, I can see that
the locks come and go. When I see no locks for DB1 and then run my
sql, I can see the locks by my sql are the only locks. Why NOLOCK hint
will make the difference for DB1 (it cuts the response time from 1.1
seconds to 0.6 seconds for 746 rows)?
Joe Young said the reason is DB1 is "busy" and DB2 is not. Why does DB1
appear to be busy, indeed, it is not busy at all?
Thank you.
Ray Mond wrote:
> I think the only option left here is to use that suggested by Joe Young i.e.
> use the (NOLOCK) hint in the queries where you do not care about open
> transactions.
>|||By any chance, is DB2 (your database, not IBM's :) in read-only mode? Also,
could you pls run SET STATISTICS IO ON in Query Analyzer, run both queries
and post the resulting messages? Then run SET STATISTICS IO OFF and run SET
STATISTICS TIME ON, rerun the queries and post the messages too? Just
curious to see the results.
Thanks.
--
Regards
Ray Mond
"qluo" <jluost1@.yahoo.com> wrote in message
news:4006BF8E.7060302@.yahoo.com...
> I did "Set transaction isolation level read uncommitted" on the database
> server, but it made no difference. I think the OLEDB used by PHP Web app
> is using "Set transaction isolation level read committed". So no
> matter what I have set on database server doesn't matter.
> I can use "NOLOCK" hint for each sql statement in the application. But
> there are too many of them and I am reluctant to do so.
>
> Again, I backed up DB1 and restore it with the new name DB2. I don't
> need to use "NOLOCK" hint on DB2. But for DB1, I have to use the hint to
> gain the same performance.
> When I check the Process/Locks in SQL Enterprise Manager, I can see that >
the locks come and go. When I see no locks for DB1 and then run my
> sql, I can see the locks by my sql are the only locks. Why NOLOCK hint
> will make the difference for DB1 (it cuts the response time from 1.1
> seconds to 0.6 seconds for 746 rows)?
> Joe Young said the reason is DB1 is "busy" and DB2 is not. Why does DB1
> appear to be busy, indeed, it is not busy at all?
> Thank you.
>
>
> Ray Mond wrote:
> > I think the only option left here is to use that suggested by Joe Young
i.e.
> > use the (NOLOCK) hint in the queries where you do not care about open
> > transactions.
> >
>
backup) the database and restore it with a new name within the same
instance, the performance gets back to normal. The size of both database
is 1,900 MB and space available of both is 1,100 MB.
Any idea about what makes the difference?so you're saying that you can:
* start with DB1
* back up DB1 and restore it as DB2
* you could then run the same exact query on DB1 and DB2. It would be fast
on DB2 and slow on DB1?
* If so... what steps do you need to do to then make DB2 slow? Or does
performance stay good forever?
Have you looked at SQL Profiler?
What are the exact performance problems you're seeing?
Are you restoring DB2 to the same disk arrays as DB1?
Is it possible that the physical files associated with DB1 have a serious
fragmentation problem?
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"qluo" <jluost1@.yahoo.com> wrote in message
news:40042824.2030705@.yahoo.com...
> I have a database that has big performance issue. I backup (complete
> backup) the database and restore it with a new name within the same
> instance, the performance gets back to normal. The size of both database
> is 1,900 MB and space available of both is 1,100 MB.
> Any idea about what makes the difference?
>|||Brian,
You are correct for the procedures I performed.
The performance problem is, the Web page with DB1 as the backend
database takes too long to load.
The query I used in Query Analyzer on both DB1 and DB2 is:
--
select count(*) from table1
--
It returns 72,000 on both database.
For DB1, it takes 220 ms consistently; for DB2, it takes 36ms consistently.
I have not looked at the fragmentation problem yet. But the data file
and log file for both databases are in the same physical location.
Brian Moran wrote:
> so you're saying that you can:
> * start with DB1
> * back up DB1 and restore it as DB2
> * you could then run the same exact query on DB1 and DB2. It would be fast
> on DB2 and slow on DB1?
> * If so... what steps do you need to do to then make DB2 slow? Or does
> performance stay good forever?
> Have you looked at SQL Profiler?
> What are the exact performance problems you're seeing?
> Are you restoring DB2 to the same disk arrays as DB1?
> Is it possible that the physical files associated with DB1 have a serious
> fragmentation problem?
>|||is DB1 a live database? ie, are people connecting to DB1
and making updates to table1?
if so, then the reason DB2 is faster for the same query is
that it knows no one has updated table1 on DB2, hence it
is safe to execute as select count(*) from table1 (NOLOCK)
while DB1 must row lock if there were recent
inserts/upd/del to table1
try
select count(*) from table1 (NOLOCK)
on both
-joe
>--Original Message--
>Brian,
>You are correct for the procedures I performed.
>The performance problem is, the Web page with DB1 as the
backend
>database takes too long to load.
>The query I used in Query Analyzer on both DB1 and DB2 is:
>--
>select count(*) from table1
>--
>It returns 72,000 on both database.
>For DB1, it takes 220 ms consistently; for DB2, it takes
36ms consistently.
>I have not looked at the fragmentation problem yet. But
the data file
>and log file for both databases are in the same physical
location.
>
>Brian Moran wrote:
>> so you're saying that you can:
>> * start with DB1
>> * back up DB1 and restore it as DB2
>> * you could then run the same exact query on DB1 and
DB2. It would be fast
>> on DB2 and slow on DB1?
>> * If so... what steps do you need to do to then make
DB2 slow? Or does
>> performance stay good forever?
>> Have you looked at SQL Profiler?
>> What are the exact performance problems you're seeing?
>> Are you restoring DB2 to the same disk arrays as DB1?
>> Is it possible that the physical files associated with
DB1 have a serious
>> fragmentation problem?
>.
>|||Your point is exactly right. I ran the query with (NOLOCK) on both DB1
and DB2 and they are taking the same amount of time, and dB1 is quicker
now than before.
So, what do I need to do on DB1 to achieve the same performance without
using (NOLOCK)?
DB1 is not actually busy and it is used by Web applications.
Thank you.
joe chang wrote:
> is DB1 a live database? ie, are people connecting to DB1
> and making updates to table1?
> if so, then the reason DB2 is faster for the same query is
> that it knows no one has updated table1 on DB2, hence it
> is safe to execute as select count(*) from table1 (NOLOCK)
> while DB1 must row lock if there were recent
> inserts/upd/del to table1
> try
> select count(*) from table1 (NOLOCK)
> on both
> -joe
>>--Original Message--
>>Brian,
>>You are correct for the procedures I performed.
>>The performance problem is, the Web page with DB1 as the
> backend
>>database takes too long to load.
>>The query I used in Query Analyzer on both DB1 and DB2 is:
>>--
>>select count(*) from table1
>>--
>>It returns 72,000 on both database.
>>For DB1, it takes 220 ms consistently; for DB2, it takes
> 36ms consistently.
>>I have not looked at the fragmentation problem yet. But
> the data file
>>and log file for both databases are in the same physical
> location.
>>Brian Moran wrote:
>>so you're saying that you can:
>>* start with DB1
>>* back up DB1 and restore it as DB2
>>* you could then run the same exact query on DB1 and
> DB2. It would be fast
>>on DB2 and slow on DB1?
>>* If so... what steps do you need to do to then make
> DB2 slow? Or does
>>performance stay good forever?
>>Have you looked at SQL Profiler?
>>What are the exact performance problems you're seeing?
>>Are you restoring DB2 to the same disk arrays as DB1?
>>Is it possible that the physical files associated with
> DB1 have a serious
>>fragmentation problem?
>>
>>.
>|||You could set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED for your
connection. There should be a trx isolation level property for your
database connectivity object. Not that this setting will be effective for
all queries passing through the connection.
--
Regards
Ray Mond|||I am reluctant to change the default (Read Committed).
My database is not busy at all. It is used by Web app and every
connection should come and go almost immediately. There shouldn't be a
concurrent(locking) issue like this.
Do you know what else I can do to minimize the locking so that I don't
have to change TRANSACTION ISOLATION LEVEL?
Thank you.
Ray Mond wrote:
> You could set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED for your
> connection. There should be a trx isolation level property for your
> database connectivity object. Not that this setting will be effective for
> all queries passing through the connection.
Your point is exactly right. I ran the query with (NOLOCK) on both DB1
and DB2 and they are taking the same amount of time, and dB1 is quicker
now than before.
So, what do I need to do on DB1 to achieve the same performance without
using (NOLOCK)?
DB1 is not actually busy and it is used by Web applications.
Thank you.
joe chang wrote:
is DB1 a live database? ie, are people connecting to DB1 and making
updates to table1?
if so, then the reason DB2 is faster for the same query is that it knows
no one has updated table1 on DB2, hence it is safe to execute as select
count(*) from table1 (NOLOCK)
while DB1 must row lock if there were recent inserts/upd/del to table1
try
select count(*) from table1 (NOLOCK)
on both
-joe
--Original Message--
Brian,
You are correct for the procedures I performed.
The performance problem is, the Web page with DB1 as the
backend
database takes too long to load.
The query I used in Query Analyzer on both DB1 and DB2 is:
--
select count(*) from table1
--
It returns 72,000 on both database.
For DB1, it takes 220 ms consistently; for DB2, it takes
36ms consistently.
I have not looked at the fragmentation problem yet. But
the data file
and log file for both databases are in the same physical
location.
Brian Moran wrote:
so you're saying that you can:
* start with DB1
* back up DB1 and restore it as DB2
* you could then run the same exact query on DB1 and
DB2. It would be fast
on DB2 and slow on DB1?
* If so... what steps do you need to do to then make
DB2 slow? Or does
performance stay good forever?
Have you looked at SQL Profiler?
What are the exact performance problems you're seeing?
Are you restoring DB2 to the same disk arrays as DB1?
Is it possible that the physical files associated with
DB1 have a serious
fragmentation problem?
.
>|||I think the only option left here is to use that suggested by Joe Young i.e.
use the (NOLOCK) hint in the queries where you do not care about open
transactions.
--
Regards
Ray Mond
"qluo" <jluost1@.yahoo.com> wrote in message
news:400554D8.8080900@.yahoo.com...
> I am reluctant to change the default (Read Committed).
> My database is not busy at all. It is used by Web app and every
> connection should come and go almost immediately. There shouldn't be a
> concurrent(locking) issue like this.
> Do you know what else I can do to minimize the locking so that I don't
> have to change TRANSACTION ISOLATION LEVEL?
> Thank you.
> Ray Mond wrote:
> > You could set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED for your
> > connection. There should be a trx isolation level property for your
> > database connectivity object. Not that this setting will be effective
for
> > all queries passing through the connection.
> Your point is exactly right. I ran the query with (NOLOCK) on both DB1
> and DB2 and they are taking the same amount of time, and dB1 is quicker
> now than before.
> So, what do I need to do on DB1 to achieve the same performance without
> using (NOLOCK)?
> DB1 is not actually busy and it is used by Web applications.
> Thank you.
>
> joe chang wrote:
> is DB1 a live database? ie, are people connecting to DB1 and making
> updates to table1?
> if so, then the reason DB2 is faster for the same query is that it knows
> no one has updated table1 on DB2, hence it is safe to execute as select
> count(*) from table1 (NOLOCK)
> while DB1 must row lock if there were recent inserts/upd/del to table1
> try
> select count(*) from table1 (NOLOCK)
> on both
> -joe
> --Original Message--
> Brian,
> You are correct for the procedures I performed.
> The performance problem is, the Web page with DB1 as the
> backend
> database takes too long to load.
> The query I used in Query Analyzer on both DB1 and DB2 is:
> --
> select count(*) from table1
> --
> It returns 72,000 on both database.
> For DB1, it takes 220 ms consistently; for DB2, it takes
> 36ms consistently.
> I have not looked at the fragmentation problem yet. But
> the data file
> and log file for both databases are in the same physical
> location.
>
> Brian Moran wrote:
> so you're saying that you can:
> * start with DB1
> * back up DB1 and restore it as DB2
> * you could then run the same exact query on DB1 and
> DB2. It would be fast
> on DB2 and slow on DB1?
> * If so... what steps do you need to do to then make
> DB2 slow? Or does
> performance stay good forever?
> Have you looked at SQL Profiler?
> What are the exact performance problems you're seeing?
> Are you restoring DB2 to the same disk arrays as DB1?
> Is it possible that the physical files associated with
> DB1 have a serious
> fragmentation problem?
>
> .
>
> >
>|||I did "Set transaction isolation level read uncommitted" on the database
server, but it made no difference. I think the OLEDB used by PHP Web app
is using "Set transaction isolation level read committed". So no
matter what I have set on database server doesn't matter.
I can use "NOLOCK" hint for each sql statement in the application. But
there are too many of them and I am reluctant to do so.
Again, I backed up DB1 and restore it with the new name DB2. I don't
need to use "NOLOCK" hint on DB2. But for DB1, I have to use the hint to
gain the same performance.
When I check the Process/Locks in SQL Enterprise Manager, I can see that
the locks come and go. When I see no locks for DB1 and then run my
sql, I can see the locks by my sql are the only locks. Why NOLOCK hint
will make the difference for DB1 (it cuts the response time from 1.1
seconds to 0.6 seconds for 746 rows)?
Joe Young said the reason is DB1 is "busy" and DB2 is not. Why does DB1
appear to be busy, indeed, it is not busy at all?
Thank you.
Ray Mond wrote:
> I think the only option left here is to use that suggested by Joe Young i.e.
> use the (NOLOCK) hint in the queries where you do not care about open
> transactions.
>|||By any chance, is DB2 (your database, not IBM's :) in read-only mode? Also,
could you pls run SET STATISTICS IO ON in Query Analyzer, run both queries
and post the resulting messages? Then run SET STATISTICS IO OFF and run SET
STATISTICS TIME ON, rerun the queries and post the messages too? Just
curious to see the results.
Thanks.
--
Regards
Ray Mond
"qluo" <jluost1@.yahoo.com> wrote in message
news:4006BF8E.7060302@.yahoo.com...
> I did "Set transaction isolation level read uncommitted" on the database
> server, but it made no difference. I think the OLEDB used by PHP Web app
> is using "Set transaction isolation level read committed". So no
> matter what I have set on database server doesn't matter.
> I can use "NOLOCK" hint for each sql statement in the application. But
> there are too many of them and I am reluctant to do so.
>
> Again, I backed up DB1 and restore it with the new name DB2. I don't
> need to use "NOLOCK" hint on DB2. But for DB1, I have to use the hint to
> gain the same performance.
> When I check the Process/Locks in SQL Enterprise Manager, I can see that >
the locks come and go. When I see no locks for DB1 and then run my
> sql, I can see the locks by my sql are the only locks. Why NOLOCK hint
> will make the difference for DB1 (it cuts the response time from 1.1
> seconds to 0.6 seconds for 746 rows)?
> Joe Young said the reason is DB1 is "busy" and DB2 is not. Why does DB1
> appear to be busy, indeed, it is not busy at all?
> Thank you.
>
>
> Ray Mond wrote:
> > I think the only option left here is to use that suggested by Joe Young
i.e.
> > use the (NOLOCK) hint in the queries where you do not care about open
> > transactions.
> >
>
"Identical" database, huge performance difference
I have a database that has big performance issue. I backup (complete
backup) the database and restore it with a new name within the same
instance, the performance gets back to normal. The size of both database
is 1,900 MB and space available of both is 1,100 MB.
Any idea about what makes the difference?so you're saying that you can:
* start with DB1
* back up DB1 and restore it as DB2
* you could then run the same exact query on DB1 and DB2. It would be fast
on DB2 and slow on DB1?
* If so... what steps do you need to do to then make DB2 slow? Or does
performance stay good forever?
Have you looked at SQL Profiler?
What are the exact performance problems you're seeing?
Are you restoring DB2 to the same disk arrays as DB1?
Is it possible that the physical files associated with DB1 have a serious
fragmentation problem?
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"qluo" <jluost1@.yahoo.com> wrote in message
news:40042824.2030705@.yahoo.com...
You are correct for the procedures I performed.
The performance problem is, the Web page with DB1 as the backend
database takes too long to load.
The query I used in Query Analyzer on both DB1 and DB2 is:
--
select count(*) from table1
--
It returns 72,000 on both database.
For DB1, it takes 220 ms consistently; for DB2, it takes 36ms consistently.
I have not looked at the fragmentation problem yet. But the data file
and log file for both databases are in the same physical location.
Brian Moran wrote:
and making updates to table1?
if so, then the reason DB2 is faster for the same query is
that it knows no one has updated table1 on DB2, hence it
is safe to execute as select count(*) from table1 (NOLOCK)
while DB1 must row lock if there were recent
inserts/upd/del to table1
try
select count(*) from table1 (NOLOCK)
on both
-joe
backend
36ms consistently.
the data file
location.
and DB2 and they are taking the same amount of time, and dB1 is quicker
now than before.
So, what do I need to do on DB1 to achieve the same performance without
using (NOLOCK)?
DB1 is not actually busy and it is used by Web applications.
Thank you.
joe chang wrote:
connection. There should be a trx isolation level property for your
database connectivity object. Not that this setting will be effective for
all queries passing through the connection.
Regards
Ray Mond|||I am reluctant to change the default (Read Committed).
My database is not busy at all. It is used by Web app and every
connection should come and go almost immediately. There shouldn't be a
concurrent(locking) issue like this.
Do you know what else I can do to minimize the locking so that I don't
have to change TRANSACTION ISOLATION LEVEL?
Thank you.
Ray Mond wrote:
Your point is exactly right. I ran the query with (NOLOCK) on both DB1
and DB2 and they are taking the same amount of time, and dB1 is quicker
now than before.
So, what do I need to do on DB1 to achieve the same performance without
using (NOLOCK)?
DB1 is not actually busy and it is used by Web applications.
Thank you.
joe chang wrote:
is DB1 a live database? ie, are people connecting to DB1 and making
updates to table1?
if so, then the reason DB2 is faster for the same query is that it knows
no one has updated table1 on DB2, hence it is safe to execute as select
count(*) from table1 (NOLOCK)
while DB1 must row lock if there were recent inserts/upd/del to table1
try
select count(*) from table1 (NOLOCK)
on both
-joe
--Original Message--
Brian,
You are correct for the procedures I performed.
The performance problem is, the Web page with DB1 as the
backend
database takes too long to load.
The query I used in Query Analyzer on both DB1 and DB2 is:
--
select count(*) from table1
--
It returns 72,000 on both database.
For DB1, it takes 220 ms consistently; for DB2, it takes
36ms consistently.
I have not looked at the fragmentation problem yet. But
the data file
and log file for both databases are in the same physical
location.
Brian Moran wrote:
so you're saying that you can:
* start with DB1
* back up DB1 and restore it as DB2
* you could then run the same exact query on DB1 and
DB2. It would be fast
on DB2 and slow on DB1?
* If so... what steps do you need to do to then make
DB2 slow? Or does
performance stay good forever?
Have you looked at SQL Profiler?
What are the exact performance problems you're seeing?
Are you restoring DB2 to the same disk arrays as DB1?
Is it possible that the physical files associated with
DB1 have a serious
fragmentation problem?
.
use the (NOLOCK) hint in the queries where you do not care about open
transactions.
Regards
Ray Mond
"qluo" <jluost1@.yahoo.com> wrote in message
news:400554D8.8080900@.yahoo.com...
server, but it made no difference. I think the OLEDB used by php Web app
is using "Set transaction isolation level read committed". So no
matter what I have set on database server doesn't matter.
I can use "NOLOCK" hint for each sql statement in the application. But
there are too many of them and I am reluctant to do so.
Again, I backed up DB1 and restore it with the new name DB2. I don't
need to use "NOLOCK" hint on DB2. But for DB1, I have to use the hint to
gain the same performance.
When I check the Process/Locks in SQL Enterprise Manager, I can see that
the locks come and go. When I see no locks for DB1 and then run my
sql, I can see the locks by my sql are the only locks. Why NOLOCK hint
will make the difference for DB1 (it cuts the response time from 1.1
seconds to 0.6 seconds for 746 rows)?
Joe Young said the reason is DB1 is "busy" and DB2 is not. Why does DB1
appear to be busy, indeed, it is not busy at all?
Thank you.
Ray Mond wrote:
in read-only mode? Also,
could you pls run SET STATISTICS IO ON in Query Analyzer, run both queries
and post the resulting messages? Then run SET STATISTICS IO OFF and run SET
STATISTICS TIME ON, rerun the queries and post the messages too? Just
curious to see the results.
Thanks.
Regards
Ray Mond
"qluo" <jluost1@.yahoo.com> wrote in message
news:4006BF8E.7060302@.yahoo.com...
the locks come and go. When I see no locks for DB1 and then run my
backup) the database and restore it with a new name within the same
instance, the performance gets back to normal. The size of both database
is 1,900 MB and space available of both is 1,100 MB.
Any idea about what makes the difference?so you're saying that you can:
* start with DB1
* back up DB1 and restore it as DB2
* you could then run the same exact query on DB1 and DB2. It would be fast
on DB2 and slow on DB1?
* If so... what steps do you need to do to then make DB2 slow? Or does
performance stay good forever?
Have you looked at SQL Profiler?
What are the exact performance problems you're seeing?
Are you restoring DB2 to the same disk arrays as DB1?
Is it possible that the physical files associated with DB1 have a serious
fragmentation problem?
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"qluo" <jluost1@.yahoo.com> wrote in message
news:40042824.2030705@.yahoo.com...
quote:|||Brian,
> I have a database that has big performance issue. I backup (complete
> backup) the database and restore it with a new name within the same
> instance, the performance gets back to normal. The size of both database
> is 1,900 MB and space available of both is 1,100 MB.
> Any idea about what makes the difference?
>
You are correct for the procedures I performed.
The performance problem is, the Web page with DB1 as the backend
database takes too long to load.
The query I used in Query Analyzer on both DB1 and DB2 is:
--
select count(*) from table1
--
It returns 72,000 on both database.
For DB1, it takes 220 ms consistently; for DB2, it takes 36ms consistently.
I have not looked at the fragmentation problem yet. But the data file
and log file for both databases are in the same physical location.
Brian Moran wrote:
quote:|||is DB1 a live database? ie, are people connecting to DB1
> so you're saying that you can:
> * start with DB1
> * back up DB1 and restore it as DB2
> * you could then run the same exact query on DB1 and DB2. It would be fast
> on DB2 and slow on DB1?
> * If so... what steps do you need to do to then make DB2 slow? Or does
> performance stay good forever?
> Have you looked at SQL Profiler?
> What are the exact performance problems you're seeing?
> Are you restoring DB2 to the same disk arrays as DB1?
> Is it possible that the physical files associated with DB1 have a serious
> fragmentation problem?
>
and making updates to table1?
if so, then the reason DB2 is faster for the same query is
that it knows no one has updated table1 on DB2, hence it
is safe to execute as select count(*) from table1 (NOLOCK)
while DB1 must row lock if there were recent
inserts/upd/del to table1
try
select count(*) from table1 (NOLOCK)
on both
-joe
quote:
>--Original Message--
>Brian,
>You are correct for the procedures I performed.
>The performance problem is, the Web page with DB1 as the
backend
quote:
>database takes too long to load.
>The query I used in Query Analyzer on both DB1 and DB2 is:
>--
>select count(*) from table1
>--
>It returns 72,000 on both database.
>For DB1, it takes 220 ms consistently; for DB2, it takes
36ms consistently.
quote:
>I have not looked at the fragmentation problem yet. But
the data file
quote:
>and log file for both databases are in the same physical
location.
quote:|||Your point is exactly right. I ran the query with (NOLOCK) on both DB1
>
>Brian Moran wrote:
DB2. It would be fast[QUOTE]
DB2 slow? Or does[QUOTE]
DB1 have a serious[QUOTE]
>.
>
and DB2 and they are taking the same amount of time, and dB1 is quicker
now than before.
So, what do I need to do on DB1 to achieve the same performance without
using (NOLOCK)?
DB1 is not actually busy and it is used by Web applications.
Thank you.
joe chang wrote:
quote:|||You could set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED for your
> is DB1 a live database? ie, are people connecting to DB1
> and making updates to table1?
> if so, then the reason DB2 is faster for the same query is
> that it knows no one has updated table1 on DB2, hence it
> is safe to execute as select count(*) from table1 (NOLOCK)
> while DB1 must row lock if there were recent
> inserts/upd/del to table1
> try
> select count(*) from table1 (NOLOCK)
> on both
> -joe
>
> backend
>
> 36ms consistently.
>
> the data file
>
> location.
>
> DB2. It would be fast
>
> DB2 slow? Or does
>
> DB1 have a serious
>
>
connection. There should be a trx isolation level property for your
database connectivity object. Not that this setting will be effective for
all queries passing through the connection.
Regards
Ray Mond|||I am reluctant to change the default (Read Committed).
My database is not busy at all. It is used by Web app and every
connection should come and go almost immediately. There shouldn't be a
concurrent(locking) issue like this.
Do you know what else I can do to minimize the locking so that I don't
have to change TRANSACTION ISOLATION LEVEL?
Thank you.
Ray Mond wrote:
quote:
> You could set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED for your
> connection. There should be a trx isolation level property for your
> database connectivity object. Not that this setting will be effective for
> all queries passing through the connection.
Your point is exactly right. I ran the query with (NOLOCK) on both DB1
and DB2 and they are taking the same amount of time, and dB1 is quicker
now than before.
So, what do I need to do on DB1 to achieve the same performance without
using (NOLOCK)?
DB1 is not actually busy and it is used by Web applications.
Thank you.
joe chang wrote:
is DB1 a live database? ie, are people connecting to DB1 and making
updates to table1?
if so, then the reason DB2 is faster for the same query is that it knows
no one has updated table1 on DB2, hence it is safe to execute as select
count(*) from table1 (NOLOCK)
while DB1 must row lock if there were recent inserts/upd/del to table1
try
select count(*) from table1 (NOLOCK)
on both
-joe
--Original Message--
Brian,
You are correct for the procedures I performed.
The performance problem is, the Web page with DB1 as the
backend
database takes too long to load.
The query I used in Query Analyzer on both DB1 and DB2 is:
--
select count(*) from table1
--
It returns 72,000 on both database.
For DB1, it takes 220 ms consistently; for DB2, it takes
36ms consistently.
I have not looked at the fragmentation problem yet. But
the data file
and log file for both databases are in the same physical
location.
Brian Moran wrote:
so you're saying that you can:
* start with DB1
* back up DB1 and restore it as DB2
* you could then run the same exact query on DB1 and
DB2. It would be fast
on DB2 and slow on DB1?
* If so... what steps do you need to do to then make
DB2 slow? Or does
performance stay good forever?
Have you looked at SQL Profiler?
What are the exact performance problems you're seeing?
Are you restoring DB2 to the same disk arrays as DB1?
Is it possible that the physical files associated with
DB1 have a serious
fragmentation problem?
.
quote:|||I think the only option left here is to use that suggested by Joe Young i.e.
>
use the (NOLOCK) hint in the queries where you do not care about open
transactions.
Regards
Ray Mond
"qluo" <jluost1@.yahoo.com> wrote in message
news:400554D8.8080900@.yahoo.com...
quote:|||I did "Set transaction isolation level read uncommitted" on the database
> I am reluctant to change the default (Read Committed).
> My database is not busy at all. It is used by Web app and every
> connection should come and go almost immediately. There shouldn't be a
> concurrent(locking) issue like this.
> Do you know what else I can do to minimize the locking so that I don't
> have to change TRANSACTION ISOLATION LEVEL?
> Thank you.
> Ray Mond wrote:
for[QUOTE]
> Your point is exactly right. I ran the query with (NOLOCK) on both DB1
> and DB2 and they are taking the same amount of time, and dB1 is quicker
> now than before.
> So, what do I need to do on DB1 to achieve the same performance without
> using (NOLOCK)?
> DB1 is not actually busy and it is used by Web applications.
> Thank you.
>
> joe chang wrote:
> is DB1 a live database? ie, are people connecting to DB1 and making
> updates to table1?
> if so, then the reason DB2 is faster for the same query is that it knows
> no one has updated table1 on DB2, hence it is safe to execute as select
> count(*) from table1 (NOLOCK)
> while DB1 must row lock if there were recent inserts/upd/del to table1
> try
> select count(*) from table1 (NOLOCK)
> on both
> -joe
> --Original Message--
> Brian,
> You are correct for the procedures I performed.
> The performance problem is, the Web page with DB1 as the
> backend
> database takes too long to load.
> The query I used in Query Analyzer on both DB1 and DB2 is:
> --
> select count(*) from table1
> --
> It returns 72,000 on both database.
> For DB1, it takes 220 ms consistently; for DB2, it takes
> 36ms consistently.
> I have not looked at the fragmentation problem yet. But
> the data file
> and log file for both databases are in the same physical
> location.
>
> Brian Moran wrote:
> so you're saying that you can:
> * start with DB1
> * back up DB1 and restore it as DB2
> * you could then run the same exact query on DB1 and
> DB2. It would be fast
> on DB2 and slow on DB1?
> * If so... what steps do you need to do to then make
> DB2 slow? Or does
> performance stay good forever?
> Have you looked at SQL Profiler?
> What are the exact performance problems you're seeing?
> Are you restoring DB2 to the same disk arrays as DB1?
> Is it possible that the physical files associated with
> DB1 have a serious
> fragmentation problem?
>
> .
>
>
>
server, but it made no difference. I think the OLEDB used by php Web app
is using "Set transaction isolation level read committed". So no
matter what I have set on database server doesn't matter.
I can use "NOLOCK" hint for each sql statement in the application. But
there are too many of them and I am reluctant to do so.
Again, I backed up DB1 and restore it with the new name DB2. I don't
need to use "NOLOCK" hint on DB2. But for DB1, I have to use the hint to
gain the same performance.
When I check the Process/Locks in SQL Enterprise Manager, I can see that
the locks come and go. When I see no locks for DB1 and then run my
sql, I can see the locks by my sql are the only locks. Why NOLOCK hint
will make the difference for DB1 (it cuts the response time from 1.1
seconds to 0.6 seconds for 746 rows)?
Joe Young said the reason is DB1 is "busy" and DB2 is not. Why does DB1
appear to be busy, indeed, it is not busy at all?
Thank you.
Ray Mond wrote:
quote:|||By any chance, is DB2 (your database, not IBM's
> I think the only option left here is to use that suggested by Joe Young i.
e.
> use the (NOLOCK) hint in the queries where you do not care about open
> transactions.
>
in read-only mode? Also,could you pls run SET STATISTICS IO ON in Query Analyzer, run both queries
and post the resulting messages? Then run SET STATISTICS IO OFF and run SET
STATISTICS TIME ON, rerun the queries and post the messages too? Just
curious to see the results.
Thanks.
Regards
Ray Mond
"qluo" <jluost1@.yahoo.com> wrote in message
news:4006BF8E.7060302@.yahoo.com...
quote:
> I did "Set transaction isolation level read uncommitted" on the database
> server, but it made no difference. I think the OLEDB used by php Web app
> is using "Set transaction isolation level read committed". So no
> matter what I have set on database server doesn't matter.
> I can use "NOLOCK" hint for each sql statement in the application. But
> there are too many of them and I am reluctant to do so.
>
quote:
> Again, I backed up DB1 and restore it with the new name DB2. I don't
> need to use "NOLOCK" hint on DB2. But for DB1, I have to use the hint to
> gain the same performance.
> When I check the Process/Locks in SQL Enterprise Manager, I can see that >
the locks come and go. When I see no locks for DB1 and then run my
quote:
> sql, I can see the locks by my sql are the only locks. Why NOLOCK hint
> will make the difference for DB1 (it cuts the response time from 1.1
> seconds to 0.6 seconds for 746 rows)?
> Joe Young said the reason is DB1 is "busy" and DB2 is not. Why does DB1
> appear to be busy, indeed, it is not busy at all?
> Thank you.
>
>
> Ray Mond wrote:
i.e.[QUOTE]
>
Thursday, February 16, 2012
"FOR XML Explicit" why poor performance, and any "quick wins" ?
Just started a new job and need a quick fix if possible.
I need to enhance performance of XML data being returned from a SProc which
uses "FOR XML EXPLICIT". (Not something I've done in 10 years as a DBA !)
Front End Application timeouts occasionally occur, which I believe can only
be caused by the SProc creating XML Data.
Without the "For XML Explicit" clause the DB returns about 5000 data rows in
approx 5 seconds.
With the "For XML Explicit" clause, same request will take 3.5 minutes !!
HOWEVER ... If I create copy of the procedure, and run the same request
parameters, it only takes about 6-8 seconds to complete.
No recompile going on as far a I can tell.
No more than 10 calls against this proc per minute.
There are several #Temp tables in the procedure.
I will improve performance by changing the whole process to return record
sets for the front end code to manipulate as required, which will take time
to integrate.
Any ideas on why this "apparent" performance issue happens ?
Any performance tweeks I can make right now will be of benefit.
Thanks
Steve ...
Doing the XML generation on the client-side may not be faster end-to-end
than using the FOR XML EXPLICIT clause.
Here are some questions:
1. You say that simply by copying and thus recompiling the stored proc, you
are getting 6-8 sec execution? But if you run the existing stored proc it
takes 3.5 mins?
If you look at the query plan, do you see any difference between them?
Can you force a recompile of the original stored proc?
It is very unusual that a for xml explicit query runs that much worse
compared to the same query without the FOR XML explicit clause. the overhead
should be more in the 1 sec range for a 5 sec query.
Best regards
Michael
"Steve [DBA-TC]" <Steve [DBA-TC]@.discussions.microsoft.com> wrote in message
news:FD6648EA-CC33-44EA-B7B2-001FEA1139CE@.microsoft.com...
> Just started a new job and need a quick fix if possible.
> I need to enhance performance of XML data being returned from a SProc
> which
> uses "FOR XML EXPLICIT". (Not something I've done in 10 years as a DBA !)
> Front End Application timeouts occasionally occur, which I believe can
> only
> be caused by the SProc creating XML Data.
> Without the "For XML Explicit" clause the DB returns about 5000 data rows
> in
> approx 5 seconds.
> With the "For XML Explicit" clause, same request will take 3.5 minutes !!
> HOWEVER ... If I create copy of the procedure, and run the same request
> parameters, it only takes about 6-8 seconds to complete.
> No recompile going on as far a I can tell.
> No more than 10 calls against this proc per minute.
> There are several #Temp tables in the procedure.
> I will improve performance by changing the whole process to return record
> sets for the front end code to manipulate as required, which will take
> time
> to integrate.
> Any ideas on why this "apparent" performance issue happens ?
> Any performance tweeks I can make right now will be of benefit.
> Thanks
> Steve ...
|||Thanks for reply,
Already done recommendations.
The only thing I can put it down to is an occasional recompile event because
of Statistics on #TempTables (Event SubClass 2-Statistics Changed).
Some data requests will be far larger than others, and it appears that these
are the ones with occasional time-outs.
Does this sound plausible, and should I create Indexes on the #TempTables in
the procedure to get round this ?
Thanks
Steve ...
"Michael Rys [MSFT]" wrote:
> Doing the XML generation on the client-side may not be faster end-to-end
> than using the FOR XML EXPLICIT clause.
> Here are some questions:
> 1. You say that simply by copying and thus recompiling the stored proc, you
> are getting 6-8 sec execution? But if you run the existing stored proc it
> takes 3.5 mins?
> If you look at the query plan, do you see any difference between them?
> Can you force a recompile of the original stored proc?
> It is very unusual that a for xml explicit query runs that much worse
> compared to the same query without the FOR XML explicit clause. the overhead
> should be more in the 1 sec range for a 5 sec query.
> Best regards
> Michael
> "Steve [DBA-TC]" <Steve [DBA-TC]@.discussions.microsoft.com> wrote in message
> news:FD6648EA-CC33-44EA-B7B2-001FEA1139CE@.microsoft.com...
>
>
|||For now I've added an option clause on each SQL Statement in the procedure.
(NB: no parallel processing so Maxdop not required)
OPTION(KEEP PLAN)
As the SProc builds #Temp Tables to use in the final query,
Would I be better off using the OPTION(KEEPFIXED PLAN) instead ?
Steve ...
"Steve [DBA-TC]" wrote:
[vbcol=seagreen]
> Thanks for reply,
> Already done recommendations.
> The only thing I can put it down to is an occasional recompile event because
> of Statistics on #TempTables (Event SubClass 2-Statistics Changed).
> Some data requests will be far larger than others, and it appears that these
> are the ones with occasional time-outs.
> Does this sound plausible, and should I create Indexes on the #TempTables in
> the procedure to get round this ?
> Thanks
> Steve ...
> "Michael Rys [MSFT]" wrote:
I need to enhance performance of XML data being returned from a SProc which
uses "FOR XML EXPLICIT". (Not something I've done in 10 years as a DBA !)
Front End Application timeouts occasionally occur, which I believe can only
be caused by the SProc creating XML Data.
Without the "For XML Explicit" clause the DB returns about 5000 data rows in
approx 5 seconds.
With the "For XML Explicit" clause, same request will take 3.5 minutes !!
HOWEVER ... If I create copy of the procedure, and run the same request
parameters, it only takes about 6-8 seconds to complete.
No recompile going on as far a I can tell.
No more than 10 calls against this proc per minute.
There are several #Temp tables in the procedure.
I will improve performance by changing the whole process to return record
sets for the front end code to manipulate as required, which will take time
to integrate.
Any ideas on why this "apparent" performance issue happens ?
Any performance tweeks I can make right now will be of benefit.
Thanks
Steve ...
Doing the XML generation on the client-side may not be faster end-to-end
than using the FOR XML EXPLICIT clause.
Here are some questions:
1. You say that simply by copying and thus recompiling the stored proc, you
are getting 6-8 sec execution? But if you run the existing stored proc it
takes 3.5 mins?
If you look at the query plan, do you see any difference between them?
Can you force a recompile of the original stored proc?
It is very unusual that a for xml explicit query runs that much worse
compared to the same query without the FOR XML explicit clause. the overhead
should be more in the 1 sec range for a 5 sec query.
Best regards
Michael
"Steve [DBA-TC]" <Steve [DBA-TC]@.discussions.microsoft.com> wrote in message
news:FD6648EA-CC33-44EA-B7B2-001FEA1139CE@.microsoft.com...
> Just started a new job and need a quick fix if possible.
> I need to enhance performance of XML data being returned from a SProc
> which
> uses "FOR XML EXPLICIT". (Not something I've done in 10 years as a DBA !)
> Front End Application timeouts occasionally occur, which I believe can
> only
> be caused by the SProc creating XML Data.
> Without the "For XML Explicit" clause the DB returns about 5000 data rows
> in
> approx 5 seconds.
> With the "For XML Explicit" clause, same request will take 3.5 minutes !!
> HOWEVER ... If I create copy of the procedure, and run the same request
> parameters, it only takes about 6-8 seconds to complete.
> No recompile going on as far a I can tell.
> No more than 10 calls against this proc per minute.
> There are several #Temp tables in the procedure.
> I will improve performance by changing the whole process to return record
> sets for the front end code to manipulate as required, which will take
> time
> to integrate.
> Any ideas on why this "apparent" performance issue happens ?
> Any performance tweeks I can make right now will be of benefit.
> Thanks
> Steve ...
|||Thanks for reply,
Already done recommendations.
The only thing I can put it down to is an occasional recompile event because
of Statistics on #TempTables (Event SubClass 2-Statistics Changed).
Some data requests will be far larger than others, and it appears that these
are the ones with occasional time-outs.
Does this sound plausible, and should I create Indexes on the #TempTables in
the procedure to get round this ?
Thanks
Steve ...
"Michael Rys [MSFT]" wrote:
> Doing the XML generation on the client-side may not be faster end-to-end
> than using the FOR XML EXPLICIT clause.
> Here are some questions:
> 1. You say that simply by copying and thus recompiling the stored proc, you
> are getting 6-8 sec execution? But if you run the existing stored proc it
> takes 3.5 mins?
> If you look at the query plan, do you see any difference between them?
> Can you force a recompile of the original stored proc?
> It is very unusual that a for xml explicit query runs that much worse
> compared to the same query without the FOR XML explicit clause. the overhead
> should be more in the 1 sec range for a 5 sec query.
> Best regards
> Michael
> "Steve [DBA-TC]" <Steve [DBA-TC]@.discussions.microsoft.com> wrote in message
> news:FD6648EA-CC33-44EA-B7B2-001FEA1139CE@.microsoft.com...
>
>
|||For now I've added an option clause on each SQL Statement in the procedure.
(NB: no parallel processing so Maxdop not required)
OPTION(KEEP PLAN)
As the SProc builds #Temp Tables to use in the final query,
Would I be better off using the OPTION(KEEPFIXED PLAN) instead ?
Steve ...
"Steve [DBA-TC]" wrote:
[vbcol=seagreen]
> Thanks for reply,
> Already done recommendations.
> The only thing I can put it down to is an occasional recompile event because
> of Statistics on #TempTables (Event SubClass 2-Statistics Changed).
> Some data requests will be far larger than others, and it appears that these
> are the ones with occasional time-outs.
> Does this sound plausible, and should I create Indexes on the #TempTables in
> the procedure to get round this ?
> Thanks
> Steve ...
> "Michael Rys [MSFT]" wrote:
"FOR XML Explicit" why poor performance, and any "quick wins" ?
Just started a new job and need a quick fix if possible.
I need to enhance performance of XML data being returned from a SProc which
uses "FOR XML EXPLICIT". (Not something I've done in 10 years as a DBA !)
Front End Application timeouts occasionally occur, which I believe can only
be caused by the SProc creating XML Data.
Without the "For XML Explicit" clause the DB returns about 5000 data rows in
approx 5 seconds.
With the "For XML Explicit" clause, same request will take 3.5 minutes !!
HOWEVER ... If I create copy of the procedure, and run the same request
parameters, it only takes about 6-8 seconds to complete.
No recompile going on as far a I can tell.
No more than 10 calls against this proc per minute.
There are several #Temp tables in the procedure.
I will improve performance by changing the whole process to return record
sets for the front end code to manipulate as required, which will take time
to integrate.
Any ideas on why this "apparent" performance issue happens ?
Any performance tw
s I can make right now will be of benefit.
Thanks
Steve ...Doing the XML generation on the client-side may not be faster end-to-end
than using the FOR XML EXPLICIT clause.
Here are some questions:
1. You say that simply by copying and thus recompiling the stored proc, you
are getting 6-8 sec execution? But if you run the existing stored proc it
takes 3.5 mins?
If you look at the query plan, do you see any difference between them?
Can you force a recompile of the original stored proc?
It is very unusual that a for xml explicit query runs that much worse
compared to the same query without the FOR XML explicit clause. the overhead
should be more in the 1 sec range for a 5 sec query.
Best regards
Michael
"Steve [DBA-TC]" <Steve [DBA-TC]@.discussions.microsoft.com> wrote in message
news:FD6648EA-CC33-44EA-B7B2-001FEA1139CE@.microsoft.com...
> Just started a new job and need a quick fix if possible.
> I need to enhance performance of XML data being returned from a SProc
> which
> uses "FOR XML EXPLICIT". (Not something I've done in 10 years as a DBA !)
> Front End Application timeouts occasionally occur, which I believe can
> only
> be caused by the SProc creating XML Data.
> Without the "For XML Explicit" clause the DB returns about 5000 data rows
> in
> approx 5 seconds.
> With the "For XML Explicit" clause, same request will take 3.5 minutes !!
> HOWEVER ... If I create copy of the procedure, and run the same request
> parameters, it only takes about 6-8 seconds to complete.
> No recompile going on as far a I can tell.
> No more than 10 calls against this proc per minute.
> There are several #Temp tables in the procedure.
> I will improve performance by changing the whole process to return record
> sets for the front end code to manipulate as required, which will take
> time
> to integrate.
> Any ideas on why this "apparent" performance issue happens ?
> Any performance tws I can make right now will be of benefit.
> Thanks
> Steve ...
I need to enhance performance of XML data being returned from a SProc which
uses "FOR XML EXPLICIT". (Not something I've done in 10 years as a DBA !)
Front End Application timeouts occasionally occur, which I believe can only
be caused by the SProc creating XML Data.
Without the "For XML Explicit" clause the DB returns about 5000 data rows in
approx 5 seconds.
With the "For XML Explicit" clause, same request will take 3.5 minutes !!
HOWEVER ... If I create copy of the procedure, and run the same request
parameters, it only takes about 6-8 seconds to complete.
No recompile going on as far a I can tell.
No more than 10 calls against this proc per minute.
There are several #Temp tables in the procedure.
I will improve performance by changing the whole process to return record
sets for the front end code to manipulate as required, which will take time
to integrate.
Any ideas on why this "apparent" performance issue happens ?
Any performance tw
s I can make right now will be of benefit.Thanks
Steve ...Doing the XML generation on the client-side may not be faster end-to-end
than using the FOR XML EXPLICIT clause.
Here are some questions:
1. You say that simply by copying and thus recompiling the stored proc, you
are getting 6-8 sec execution? But if you run the existing stored proc it
takes 3.5 mins?
If you look at the query plan, do you see any difference between them?
Can you force a recompile of the original stored proc?
It is very unusual that a for xml explicit query runs that much worse
compared to the same query without the FOR XML explicit clause. the overhead
should be more in the 1 sec range for a 5 sec query.
Best regards
Michael
"Steve [DBA-TC]" <Steve [DBA-TC]@.discussions.microsoft.com> wrote in message
news:FD6648EA-CC33-44EA-B7B2-001FEA1139CE@.microsoft.com...
> Just started a new job and need a quick fix if possible.
> I need to enhance performance of XML data being returned from a SProc
> which
> uses "FOR XML EXPLICIT". (Not something I've done in 10 years as a DBA !)
> Front End Application timeouts occasionally occur, which I believe can
> only
> be caused by the SProc creating XML Data.
> Without the "For XML Explicit" clause the DB returns about 5000 data rows
> in
> approx 5 seconds.
> With the "For XML Explicit" clause, same request will take 3.5 minutes !!
> HOWEVER ... If I create copy of the procedure, and run the same request
> parameters, it only takes about 6-8 seconds to complete.
> No recompile going on as far a I can tell.
> No more than 10 calls against this proc per minute.
> There are several #Temp tables in the procedure.
> I will improve performance by changing the whole process to return record
> sets for the front end code to manipulate as required, which will take
> time
> to integrate.
> Any ideas on why this "apparent" performance issue happens ?
> Any performance tw
s I can make right now will be of benefit.> Thanks
> Steve ...
"FOR XML Explicit" why poor performance, and any "quick wins"
Thanks for reply,
Already done recommendations.
The only thing I can put it down to is an occasional recompile event because
of Statistics on #TempTables (Event SubClass 2-Statistics Changed).
Some data requests will be far larger than others, and it appears that these
are the ones with occasional time-outs.
Does this sound plausible, and should I create Indexes on the #TempTables in
the procedure to get round this ?
Thanks
Steve ...
"Michael Rys [MSFT]" wrote:
> Doing the XML generation on the client-side may not be faster end-to-end
> than using the FOR XML EXPLICIT clause.
> Here are some questions:
> 1. You say that simply by copying and thus recompiling the stored proc, yo
u
> are getting 6-8 sec execution? But if you run the existing stored proc it
> takes 3.5 mins?
> If you look at the query plan, do you see any difference between them?
> Can you force a recompile of the original stored proc?
> It is very unusual that a for xml explicit query runs that much worse
> compared to the same query without the FOR XML explicit clause. the overhe
ad
> should be more in the 1 sec range for a 5 sec query.
> Best regards
> Michael
> "Steve [DBA-TC]" <Steve [DBA-TC]@.discussions.microsoft.com> wrote in messa
ge
> news:FD6648EA-CC33-44EA-B7B2-001FEA1139CE@.microsoft.com...
>
>For now I've added an option clause on each SQL Statement in the procedure.
(NB: no parallel processing so Maxdop not required)
OPTION(KEEP PLAN)
As the SProc builds #Temp Tables to use in the final query,
Would I be better off using the OPTION(KEEPFIXED PLAN) instead ?
Steve ...
"Steve [DBA-TC]" wrote:
> Thanks for reply,
> Already done recommendations.
> The only thing I can put it down to is an occasional recompile event becau
se
> of Statistics on #TempTables (Event SubClass 2-Statistics Changed).
> Some data requests will be far larger than others, and it appears that the
se
> are the ones with occasional time-outs.
> Does this sound plausible, and should I create Indexes on the #TempTables
in
> the procedure to get round this ?
> Thanks
> Steve ...
> "Michael Rys [MSFT]" wrote:
>
Already done recommendations.
The only thing I can put it down to is an occasional recompile event because
of Statistics on #TempTables (Event SubClass 2-Statistics Changed).
Some data requests will be far larger than others, and it appears that these
are the ones with occasional time-outs.
Does this sound plausible, and should I create Indexes on the #TempTables in
the procedure to get round this ?
Thanks
Steve ...
"Michael Rys [MSFT]" wrote:
> Doing the XML generation on the client-side may not be faster end-to-end
> than using the FOR XML EXPLICIT clause.
> Here are some questions:
> 1. You say that simply by copying and thus recompiling the stored proc, yo
u
> are getting 6-8 sec execution? But if you run the existing stored proc it
> takes 3.5 mins?
> If you look at the query plan, do you see any difference between them?
> Can you force a recompile of the original stored proc?
> It is very unusual that a for xml explicit query runs that much worse
> compared to the same query without the FOR XML explicit clause. the overhe
ad
> should be more in the 1 sec range for a 5 sec query.
> Best regards
> Michael
> "Steve [DBA-TC]" <Steve [DBA-TC]@.discussions.microsoft.com> wrote in messa
ge
> news:FD6648EA-CC33-44EA-B7B2-001FEA1139CE@.microsoft.com...
>
>For now I've added an option clause on each SQL Statement in the procedure.
(NB: no parallel processing so Maxdop not required)
OPTION(KEEP PLAN)
As the SProc builds #Temp Tables to use in the final query,
Would I be better off using the OPTION(KEEPFIXED PLAN) instead ?
Steve ...
"Steve [DBA-TC]" wrote:
> Thanks for reply,
> Already done recommendations.
> The only thing I can put it down to is an occasional recompile event becau
se
> of Statistics on #TempTables (Event SubClass 2-Statistics Changed).
> Some data requests will be far larger than others, and it appears that the
se
> are the ones with occasional time-outs.
> Does this sound plausible, and should I create Indexes on the #TempTables
in
> the procedure to get round this ?
> Thanks
> Steve ...
> "Michael Rys [MSFT]" wrote:
>
Thursday, February 9, 2012
"Auto Create Statistics" make queries run (really) slower
Hello,
I'm experiencing a strange problem with query performance runing on
SQL2005. The database has 10+ tables, but we need to run really
specific queries in only 1 table with these caracteristics :
- 1 million rows
- we run everyday a few thousands queries on that table, each query
is unique (adhoc plan), and not parameterizable. (we cannot optimize
this)
- rows have a lot of nvarchar data
- all queries use a lot of LIKE / NOT LIKE statement (we cannot find
any work-around to that point, Fulltext is not adequate in that case)
- when LIKE operations are performed on columns, we always create a
duplicate column to optimize some search stuff, like putting
everything in Low Case, using Latin1_General_BIN collation, ...
- we have some indexes on short nvarchar columns, only those where we
use an exact '=' statemen
- we have another index on a float column
- all usefull indexes and statistics are manually created on that
table
- the nvarchar content of the table changes only once a day. It means
we do all optimization (indexes / stats) just after the update, and
there is no change on nvarchar data until the next update (24 hours
later)
I found that when "Auto Create Statistics" is enabled on the database,
that queries are really runing slower :
- "Auto Create Statistics" enabled : 57 min to run all queries
- "Auto Create Statistics" disabled and all auto-created stats
deleted : 7 min to run the same queries
It means that queries are running 8x slower when "Auto Create
Statistics" is enabled!
Another interesting point : just after disabling "Auto Create
Statistics", the queries continue to perform slowly until I manually
delete all statistics created automatically for that table (the one
begining with "_WA_Sys_"). It could mean that it's not a stat creation
issue, but only the existence of that statistics that could change the
query plan. But in both cases, the execution plan for the same query
seems to be exactly the same (same aspect, same costs). I also tried
to enable the Async stats update : no change.
The problem is that for all the other tables in the database, the
"Auto Create Statistics" is a good thing and useful. But not for that
specific table. Two questions :
- Is it possible to disable "Auto Create Statistics" on a specific
table? (I did not find anything about that in the BOL)
- If not, is there another work-around to deal with that kind of
performance drop?
Thanks.We need the query plan with before and after to tell you why. It sounds like
there was an inaccurate estimate which might be fixed with a larger sample
than the default but that is a guess. SQL Server 2005 keeps better stats on
string column and it may be able to do a seek on a covering index in a LIKE
query especially with a larger sample. It just needs to be tested heavily.
--
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
<pinformaticien@.yahoo.fr> wrote in message
news:8384b744-0444-4f91-a1ce-b4d302317302@.13g2000hsb.googlegroups.com...
> Hello,
> I'm experiencing a strange problem with query performance runing on
> SQL2005. The database has 10+ tables, but we need to run really
> specific queries in only 1 table with these caracteristics :
> - 1 million rows
> - we run everyday a few thousands queries on that table, each query
> is unique (adhoc plan), and not parameterizable. (we cannot optimize
> this)
> - rows have a lot of nvarchar data
> - all queries use a lot of LIKE / NOT LIKE statement (we cannot find
> any work-around to that point, Fulltext is not adequate in that case)
> - when LIKE operations are performed on columns, we always create a
> duplicate column to optimize some search stuff, like putting
> everything in Low Case, using Latin1_General_BIN collation, ...
> - we have some indexes on short nvarchar columns, only those where we
> use an exact '=' statemen
> - we have another index on a float column
> - all usefull indexes and statistics are manually created on that
> table
> - the nvarchar content of the table changes only once a day. It means
> we do all optimization (indexes / stats) just after the update, and
> there is no change on nvarchar data until the next update (24 hours
> later)
> I found that when "Auto Create Statistics" is enabled on the database,
> that queries are really runing slower :
> - "Auto Create Statistics" enabled : 57 min to run all queries
> - "Auto Create Statistics" disabled and all auto-created stats
> deleted : 7 min to run the same queries
> It means that queries are running 8x slower when "Auto Create
> Statistics" is enabled!
> Another interesting point : just after disabling "Auto Create
> Statistics", the queries continue to perform slowly until I manually
> delete all statistics created automatically for that table (the one
> begining with "_WA_Sys_"). It could mean that it's not a stat creation
> issue, but only the existence of that statistics that could change the
> query plan. But in both cases, the execution plan for the same query
> seems to be exactly the same (same aspect, same costs). I also tried
> to enable the Async stats update : no change.
> The problem is that for all the other tables in the database, the
> "Auto Create Statistics" is a good thing and useful. But not for that
> specific table. Two questions :
> - Is it possible to disable "Auto Create Statistics" on a specific
> table? (I did not find anything about that in the BOL)
> - If not, is there another work-around to deal with that kind of
> performance drop?
> Thanks.|||For what I've tried, creating then updating statistics on nvarchar
columns with the "WITH FULLSCAN" clause doesn't help. But here are
some interesting results : I setup a test server, and ran 2 times 10
queries, first time with "Auto Create Statistics" enabled, second time
with "Auto Create Statistics" disabled. Between the 2 tests, I deleted
all the automatically created statistics (the one begining with
"_WA_Sys_"), then restarted SQL server service. Here are the results
for the following query
Select * from sys.dm_exec_query_optimizer_info where counter in
('optimizations','elapsed time')
"Auto Create Statistics" enabled
optimizations 11 1
elapsed time 11 2,80751895306448
"Auto Create Statistics" disabled
optimizations 11 1
elapsed time 11 0,0665338534973798
It confirms that all the performance drop goes in optimization time
(2.8 sec average vs 0.07 sec), that finally almost doesn't otimize
anything in my case (it leads to the same execution plan is the same
is both cases). It means I need to find a way to disable / reduce that
optimization time when "Auto Create Statistics" is enabled. Any idea?
Is it possible to disable "Auto Create Statistics" on a specific
table?|||It sounds like you are right. It sounds like optimizer is spending more time
try to compile since there are more options only to come up with the same
plan. You can disable autostats on a particular table with UPDATE STATISTICS
.. WITH NORECOMPUTE.
--
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
<pinformaticien@.yahoo.fr> wrote in message
news:1e199977-1bc2-4614-ad72-036bb3111ce6@.d21g2000prf.googlegroups.com...
> For what I've tried, creating then updating statistics on nvarchar
> columns with the "WITH FULLSCAN" clause doesn't help. But here are
> some interesting results : I setup a test server, and ran 2 times 10
> queries, first time with "Auto Create Statistics" enabled, second time
> with "Auto Create Statistics" disabled. Between the 2 tests, I deleted
> all the automatically created statistics (the one begining with
> "_WA_Sys_"), then restarted SQL server service. Here are the results
> for the following query
> Select * from sys.dm_exec_query_optimizer_info where counter in
> ('optimizations','elapsed time')
> "Auto Create Statistics" enabled
> optimizations 11 1
> elapsed time 11 2,80751895306448
> "Auto Create Statistics" disabled
> optimizations 11 1
> elapsed time 11 0,0665338534973798
> It confirms that all the performance drop goes in optimization time
> (2.8 sec average vs 0.07 sec), that finally almost doesn't otimize
> anything in my case (it leads to the same execution plan is the same
> is both cases). It means I need to find a way to disable / reduce that
> optimization time when "Auto Create Statistics" is enabled. Any idea?
> Is it possible to disable "Auto Create Statistics" on a specific
> table?|||Thanks for the reply. UPDATE STATISTICS ... WITH NORECOMPUTE would
just avoid statistics to be updated. In my case, it's not the stat
update which is problematic, but the existence of the automatically
created statistics (as they badly influence the query optimizer on
that table). One solution could be to move that table on a dedicated
database and turn "Auto Create Statistics" OFF, but we would like to
avoid this solution.
I'm really surprised that SQL Server doesn't allow to disable
automatic creation of statistics on a per table basis. That could be
just really helpful in some cases.|||Ok I think I've got an interesting workaround. As we cannot disable
autocreate statistics for a specific table, the idea is to update
those unwanted stats with two clauses :
- SAMPLE 0 ROWS : to empty the statistics, so that they don't
infuence the query optimizer anymore.
- NORECOMPUTE : to avoid the "auto update stats" option to repopulate
them later
Here is the SQL statement I wrote to do this automatically on SQL 2005
(you just need to set @.dbtname correctly). It's just necessary to run
it from time to time, to ensure that new autocreated stats are
disabled.
The first tests shows exactly the same performance compared to queries
with "auto create stats" disabled.
DECLARE @.dbtname NVARCHAR(255)
SET @.dbtname = 'You_Table_Name_Here'
DECLARE c CURSOR FOR
SELECT name FROM sys.stats WHERE object_id = object_id(@.dbtname) AND
auto_created = 1
DECLARE @.statname NVARCHAR(255)
OPEN c
FETCH next FROM c INTO @.statname
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT @.statname
EXEC ('UPDATE STATISTICS ' + @.dbtname + ' (' + @.statname + ') WITH
SAMPLE 0 ROWS, NORECOMPUTE')
FETCH NEXT FROM c INTO @.statname
END
CLOSE c
DEALLOCATE c|||pinformaticien@.yahoo.fr wrote:
> Ok I think I've got an interesting workaround. As we cannot disable
> autocreate statistics for a specific table, the idea is to update
> those unwanted stats with two clauses :
> - SAMPLE 0 ROWS : to empty the statistics, so that they don't
> infuence the query optimizer anymore.
> - NORECOMPUTE : to avoid the "auto update stats" option to repopulate
> them later
> Here is the SQL statement I wrote to do this automatically on SQL 2005
> (you just need to set @.dbtname correctly). It's just necessary to run
> it from time to time, to ensure that new autocreated stats are
> disabled.
> The first tests shows exactly the same performance compared to queries
> with "auto create stats" disabled.
> DECLARE @.dbtname NVARCHAR(255)
> SET @.dbtname = 'You_Table_Name_Here'
> DECLARE c CURSOR FOR
> SELECT name FROM sys.stats WHERE object_id = object_id(@.dbtname) AND
> auto_created = 1
> DECLARE @.statname NVARCHAR(255)
> OPEN c
> FETCH next FROM c INTO @.statname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> PRINT @.statname
> EXEC ('UPDATE STATISTICS ' + @.dbtname + ' (' + @.statname + ') WITH
> SAMPLE 0 ROWS, NORECOMPUTE')
> FETCH NEXT FROM c INTO @.statname
> END
> CLOSE c
> DEALLOCATE c
Auto create statistics is only invoked if there are no existing
statistics for that column. So another solution to prevent automatic
statistics creation for a particular column is to manually create
statistics before the query is run that triggers the statistics
creation.
It could be as simple as:
create statistics ST_Test on dbo.test(my_column) with sample 0 rows,
norecompute
before running a query like
select * from dbo.test where my_column = 5
--
Gert-Jan
I'm experiencing a strange problem with query performance runing on
SQL2005. The database has 10+ tables, but we need to run really
specific queries in only 1 table with these caracteristics :
- 1 million rows
- we run everyday a few thousands queries on that table, each query
is unique (adhoc plan), and not parameterizable. (we cannot optimize
this)
- rows have a lot of nvarchar data
- all queries use a lot of LIKE / NOT LIKE statement (we cannot find
any work-around to that point, Fulltext is not adequate in that case)
- when LIKE operations are performed on columns, we always create a
duplicate column to optimize some search stuff, like putting
everything in Low Case, using Latin1_General_BIN collation, ...
- we have some indexes on short nvarchar columns, only those where we
use an exact '=' statemen
- we have another index on a float column
- all usefull indexes and statistics are manually created on that
table
- the nvarchar content of the table changes only once a day. It means
we do all optimization (indexes / stats) just after the update, and
there is no change on nvarchar data until the next update (24 hours
later)
I found that when "Auto Create Statistics" is enabled on the database,
that queries are really runing slower :
- "Auto Create Statistics" enabled : 57 min to run all queries
- "Auto Create Statistics" disabled and all auto-created stats
deleted : 7 min to run the same queries
It means that queries are running 8x slower when "Auto Create
Statistics" is enabled!
Another interesting point : just after disabling "Auto Create
Statistics", the queries continue to perform slowly until I manually
delete all statistics created automatically for that table (the one
begining with "_WA_Sys_"). It could mean that it's not a stat creation
issue, but only the existence of that statistics that could change the
query plan. But in both cases, the execution plan for the same query
seems to be exactly the same (same aspect, same costs). I also tried
to enable the Async stats update : no change.
The problem is that for all the other tables in the database, the
"Auto Create Statistics" is a good thing and useful. But not for that
specific table. Two questions :
- Is it possible to disable "Auto Create Statistics" on a specific
table? (I did not find anything about that in the BOL)
- If not, is there another work-around to deal with that kind of
performance drop?
Thanks.We need the query plan with before and after to tell you why. It sounds like
there was an inaccurate estimate which might be fixed with a larger sample
than the default but that is a guess. SQL Server 2005 keeps better stats on
string column and it may be able to do a seek on a covering index in a LIKE
query especially with a larger sample. It just needs to be tested heavily.
--
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
<pinformaticien@.yahoo.fr> wrote in message
news:8384b744-0444-4f91-a1ce-b4d302317302@.13g2000hsb.googlegroups.com...
> Hello,
> I'm experiencing a strange problem with query performance runing on
> SQL2005. The database has 10+ tables, but we need to run really
> specific queries in only 1 table with these caracteristics :
> - 1 million rows
> - we run everyday a few thousands queries on that table, each query
> is unique (adhoc plan), and not parameterizable. (we cannot optimize
> this)
> - rows have a lot of nvarchar data
> - all queries use a lot of LIKE / NOT LIKE statement (we cannot find
> any work-around to that point, Fulltext is not adequate in that case)
> - when LIKE operations are performed on columns, we always create a
> duplicate column to optimize some search stuff, like putting
> everything in Low Case, using Latin1_General_BIN collation, ...
> - we have some indexes on short nvarchar columns, only those where we
> use an exact '=' statemen
> - we have another index on a float column
> - all usefull indexes and statistics are manually created on that
> table
> - the nvarchar content of the table changes only once a day. It means
> we do all optimization (indexes / stats) just after the update, and
> there is no change on nvarchar data until the next update (24 hours
> later)
> I found that when "Auto Create Statistics" is enabled on the database,
> that queries are really runing slower :
> - "Auto Create Statistics" enabled : 57 min to run all queries
> - "Auto Create Statistics" disabled and all auto-created stats
> deleted : 7 min to run the same queries
> It means that queries are running 8x slower when "Auto Create
> Statistics" is enabled!
> Another interesting point : just after disabling "Auto Create
> Statistics", the queries continue to perform slowly until I manually
> delete all statistics created automatically for that table (the one
> begining with "_WA_Sys_"). It could mean that it's not a stat creation
> issue, but only the existence of that statistics that could change the
> query plan. But in both cases, the execution plan for the same query
> seems to be exactly the same (same aspect, same costs). I also tried
> to enable the Async stats update : no change.
> The problem is that for all the other tables in the database, the
> "Auto Create Statistics" is a good thing and useful. But not for that
> specific table. Two questions :
> - Is it possible to disable "Auto Create Statistics" on a specific
> table? (I did not find anything about that in the BOL)
> - If not, is there another work-around to deal with that kind of
> performance drop?
> Thanks.|||For what I've tried, creating then updating statistics on nvarchar
columns with the "WITH FULLSCAN" clause doesn't help. But here are
some interesting results : I setup a test server, and ran 2 times 10
queries, first time with "Auto Create Statistics" enabled, second time
with "Auto Create Statistics" disabled. Between the 2 tests, I deleted
all the automatically created statistics (the one begining with
"_WA_Sys_"), then restarted SQL server service. Here are the results
for the following query
Select * from sys.dm_exec_query_optimizer_info where counter in
('optimizations','elapsed time')
"Auto Create Statistics" enabled
optimizations 11 1
elapsed time 11 2,80751895306448
"Auto Create Statistics" disabled
optimizations 11 1
elapsed time 11 0,0665338534973798
It confirms that all the performance drop goes in optimization time
(2.8 sec average vs 0.07 sec), that finally almost doesn't otimize
anything in my case (it leads to the same execution plan is the same
is both cases). It means I need to find a way to disable / reduce that
optimization time when "Auto Create Statistics" is enabled. Any idea?
Is it possible to disable "Auto Create Statistics" on a specific
table?|||It sounds like you are right. It sounds like optimizer is spending more time
try to compile since there are more options only to come up with the same
plan. You can disable autostats on a particular table with UPDATE STATISTICS
.. WITH NORECOMPUTE.
--
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
<pinformaticien@.yahoo.fr> wrote in message
news:1e199977-1bc2-4614-ad72-036bb3111ce6@.d21g2000prf.googlegroups.com...
> For what I've tried, creating then updating statistics on nvarchar
> columns with the "WITH FULLSCAN" clause doesn't help. But here are
> some interesting results : I setup a test server, and ran 2 times 10
> queries, first time with "Auto Create Statistics" enabled, second time
> with "Auto Create Statistics" disabled. Between the 2 tests, I deleted
> all the automatically created statistics (the one begining with
> "_WA_Sys_"), then restarted SQL server service. Here are the results
> for the following query
> Select * from sys.dm_exec_query_optimizer_info where counter in
> ('optimizations','elapsed time')
> "Auto Create Statistics" enabled
> optimizations 11 1
> elapsed time 11 2,80751895306448
> "Auto Create Statistics" disabled
> optimizations 11 1
> elapsed time 11 0,0665338534973798
> It confirms that all the performance drop goes in optimization time
> (2.8 sec average vs 0.07 sec), that finally almost doesn't otimize
> anything in my case (it leads to the same execution plan is the same
> is both cases). It means I need to find a way to disable / reduce that
> optimization time when "Auto Create Statistics" is enabled. Any idea?
> Is it possible to disable "Auto Create Statistics" on a specific
> table?|||Thanks for the reply. UPDATE STATISTICS ... WITH NORECOMPUTE would
just avoid statistics to be updated. In my case, it's not the stat
update which is problematic, but the existence of the automatically
created statistics (as they badly influence the query optimizer on
that table). One solution could be to move that table on a dedicated
database and turn "Auto Create Statistics" OFF, but we would like to
avoid this solution.
I'm really surprised that SQL Server doesn't allow to disable
automatic creation of statistics on a per table basis. That could be
just really helpful in some cases.|||Ok I think I've got an interesting workaround. As we cannot disable
autocreate statistics for a specific table, the idea is to update
those unwanted stats with two clauses :
- SAMPLE 0 ROWS : to empty the statistics, so that they don't
infuence the query optimizer anymore.
- NORECOMPUTE : to avoid the "auto update stats" option to repopulate
them later
Here is the SQL statement I wrote to do this automatically on SQL 2005
(you just need to set @.dbtname correctly). It's just necessary to run
it from time to time, to ensure that new autocreated stats are
disabled.
The first tests shows exactly the same performance compared to queries
with "auto create stats" disabled.
DECLARE @.dbtname NVARCHAR(255)
SET @.dbtname = 'You_Table_Name_Here'
DECLARE c CURSOR FOR
SELECT name FROM sys.stats WHERE object_id = object_id(@.dbtname) AND
auto_created = 1
DECLARE @.statname NVARCHAR(255)
OPEN c
FETCH next FROM c INTO @.statname
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT @.statname
EXEC ('UPDATE STATISTICS ' + @.dbtname + ' (' + @.statname + ') WITH
SAMPLE 0 ROWS, NORECOMPUTE')
FETCH NEXT FROM c INTO @.statname
END
CLOSE c
DEALLOCATE c|||pinformaticien@.yahoo.fr wrote:
> Ok I think I've got an interesting workaround. As we cannot disable
> autocreate statistics for a specific table, the idea is to update
> those unwanted stats with two clauses :
> - SAMPLE 0 ROWS : to empty the statistics, so that they don't
> infuence the query optimizer anymore.
> - NORECOMPUTE : to avoid the "auto update stats" option to repopulate
> them later
> Here is the SQL statement I wrote to do this automatically on SQL 2005
> (you just need to set @.dbtname correctly). It's just necessary to run
> it from time to time, to ensure that new autocreated stats are
> disabled.
> The first tests shows exactly the same performance compared to queries
> with "auto create stats" disabled.
> DECLARE @.dbtname NVARCHAR(255)
> SET @.dbtname = 'You_Table_Name_Here'
> DECLARE c CURSOR FOR
> SELECT name FROM sys.stats WHERE object_id = object_id(@.dbtname) AND
> auto_created = 1
> DECLARE @.statname NVARCHAR(255)
> OPEN c
> FETCH next FROM c INTO @.statname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> PRINT @.statname
> EXEC ('UPDATE STATISTICS ' + @.dbtname + ' (' + @.statname + ') WITH
> SAMPLE 0 ROWS, NORECOMPUTE')
> FETCH NEXT FROM c INTO @.statname
> END
> CLOSE c
> DEALLOCATE c
Auto create statistics is only invoked if there are no existing
statistics for that column. So another solution to prevent automatic
statistics creation for a particular column is to manually create
statistics before the query is run that triggers the statistics
creation.
It could be as simple as:
create statistics ST_Test on dbo.test(my_column) with sample 0 rows,
norecompute
before running a query like
select * from dbo.test where my_column = 5
--
Gert-Jan
"Auto Create Statistics" make queries run (really) slower
Hello,
I'm experiencing a strange problem with query performance runing on
SQL2005. The database has 10+ tables, but we need to run really
specific queries in only 1 table with these caracteristics :
- 1 million rows
- we run everyday a few thousands queries on that table, each query
is unique (adhoc plan), and not parameterizable. (we cannot optimize
this)
- rows have a lot of nvarchar data
- all queries use a lot of LIKE / NOT LIKE statement (we cannot find
any work-around to that point, Fulltext is not adequate in that case)
- when LIKE operations are performed on columns, we always create a
duplicate column to optimize some search stuff, like putting
everything in Low Case, using Latin1_General_BIN collation, ...
- we have some indexes on short nvarchar columns, only those where we
use an exact '=' statemen
- we have another index on a float column
- all usefull indexes and statistics are manually created on that
table
- the nvarchar content of the table changes only once a day. It means
we do all optimization (indexes / stats) just after the update, and
there is no change on nvarchar data until the next update (24 hours
later)
I found that when "Auto Create Statistics" is enabled on the database,
that queries are really runing slower :
- "Auto Create Statistics" enabled : 57 min to run all queries
- "Auto Create Statistics" disabled and all auto-created stats
deleted : 7 min to run the same queries
It means that queries are running 8x slower when "Auto Create
Statistics" is enabled!
Another interesting point : just after disabling "Auto Create
Statistics", the queries continue to perform slowly until I manually
delete all statistics created automatically for that table (the one
begining with "_WA_Sys_"). It could mean that it's not a stat creation
issue, but only the existence of that statistics that could change the
query plan. But in both cases, the execution plan for the same query
seems to be exactly the same (same aspect, same costs). I also tried
to enable the Async stats update : no change.
The problem is that for all the other tables in the database, the
"Auto Create Statistics" is a good thing and useful. But not for that
specific table. Two questions :
- Is it possible to disable "Auto Create Statistics" on a specific
table? (I did not find anything about that in the BOL)
- If not, is there another work-around to deal with that kind of
performance drop?
Thanks.
We need the query plan with before and after to tell you why. It sounds like
there was an inaccurate estimate which might be fixed with a larger sample
than the default but that is a guess. SQL Server 2005 keeps better stats on
string column and it may be able to do a seek on a covering index in a LIKE
query especially with a larger sample. It just needs to be tested heavily.
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
<pinformaticien@.yahoo.fr> wrote in message
news:8384b744-0444-4f91-a1ce-b4d302317302@.13g2000hsb.googlegroups.com...
> Hello,
> I'm experiencing a strange problem with query performance runing on
> SQL2005. The database has 10+ tables, but we need to run really
> specific queries in only 1 table with these caracteristics :
> - 1 million rows
> - we run everyday a few thousands queries on that table, each query
> is unique (adhoc plan), and not parameterizable. (we cannot optimize
> this)
> - rows have a lot of nvarchar data
> - all queries use a lot of LIKE / NOT LIKE statement (we cannot find
> any work-around to that point, Fulltext is not adequate in that case)
> - when LIKE operations are performed on columns, we always create a
> duplicate column to optimize some search stuff, like putting
> everything in Low Case, using Latin1_General_BIN collation, ...
> - we have some indexes on short nvarchar columns, only those where we
> use an exact '=' statemen
> - we have another index on a float column
> - all usefull indexes and statistics are manually created on that
> table
> - the nvarchar content of the table changes only once a day. It means
> we do all optimization (indexes / stats) just after the update, and
> there is no change on nvarchar data until the next update (24 hours
> later)
> I found that when "Auto Create Statistics" is enabled on the database,
> that queries are really runing slower :
> - "Auto Create Statistics" enabled : 57 min to run all queries
> - "Auto Create Statistics" disabled and all auto-created stats
> deleted : 7 min to run the same queries
> It means that queries are running 8x slower when "Auto Create
> Statistics" is enabled!
> Another interesting point : just after disabling "Auto Create
> Statistics", the queries continue to perform slowly until I manually
> delete all statistics created automatically for that table (the one
> begining with "_WA_Sys_"). It could mean that it's not a stat creation
> issue, but only the existence of that statistics that could change the
> query plan. But in both cases, the execution plan for the same query
> seems to be exactly the same (same aspect, same costs). I also tried
> to enable the Async stats update : no change.
> The problem is that for all the other tables in the database, the
> "Auto Create Statistics" is a good thing and useful. But not for that
> specific table. Two questions :
> - Is it possible to disable "Auto Create Statistics" on a specific
> table? (I did not find anything about that in the BOL)
> - If not, is there another work-around to deal with that kind of
> performance drop?
> Thanks.
|||For what I've tried, creating then updating statistics on nvarchar
columns with the "WITH FULLSCAN" clause doesn't help. But here are
some interesting results : I setup a test server, and ran 2 times 10
queries, first time with "Auto Create Statistics" enabled, second time
with "Auto Create Statistics" disabled. Between the 2 tests, I deleted
all the automatically created statistics (the one begining with
"_WA_Sys_"), then restarted SQL server service. Here are the results
for the following query
Select * from sys.dm_exec_query_optimizer_info where counter in
('optimizations','elapsed time')
"Auto Create Statistics" enabled
optimizations 11 1
elapsed time 11 2,80751895306448
"Auto Create Statistics" disabled
optimizations 11 1
elapsed time 11 0,0665338534973798
It confirms that all the performance drop goes in optimization time
(2.8 sec average vs 0.07 sec), that finally almost doesn't otimize
anything in my case (it leads to the same execution plan is the same
is both cases). It means I need to find a way to disable / reduce that
optimization time when "Auto Create Statistics" is enabled. Any idea?
Is it possible to disable "Auto Create Statistics" on a specific
table?
|||It sounds like you are right. It sounds like optimizer is spending more time
try to compile since there are more options only to come up with the same
plan. You can disable autostats on a particular table with UPDATE STATISTICS
... WITH NORECOMPUTE.
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
<pinformaticien@.yahoo.fr> wrote in message
news:1e199977-1bc2-4614-ad72-036bb3111ce6@.d21g2000prf.googlegroups.com...
> For what I've tried, creating then updating statistics on nvarchar
> columns with the "WITH FULLSCAN" clause doesn't help. But here are
> some interesting results : I setup a test server, and ran 2 times 10
> queries, first time with "Auto Create Statistics" enabled, second time
> with "Auto Create Statistics" disabled. Between the 2 tests, I deleted
> all the automatically created statistics (the one begining with
> "_WA_Sys_"), then restarted SQL server service. Here are the results
> for the following query
> Select * from sys.dm_exec_query_optimizer_info where counter in
> ('optimizations','elapsed time')
> "Auto Create Statistics" enabled
> optimizations 11 1
> elapsed time 11 2,80751895306448
> "Auto Create Statistics" disabled
> optimizations 11 1
> elapsed time 11 0,0665338534973798
> It confirms that all the performance drop goes in optimization time
> (2.8 sec average vs 0.07 sec), that finally almost doesn't otimize
> anything in my case (it leads to the same execution plan is the same
> is both cases). It means I need to find a way to disable / reduce that
> optimization time when "Auto Create Statistics" is enabled. Any idea?
> Is it possible to disable "Auto Create Statistics" on a specific
> table?
|||Thanks for the reply. UPDATE STATISTICS ... WITH NORECOMPUTE would
just avoid statistics to be updated. In my case, it's not the stat
update which is problematic, but the existence of the automatically
created statistics (as they badly influence the query optimizer on
that table). One solution could be to move that table on a dedicated
database and turn "Auto Create Statistics" OFF, but we would like to
avoid this solution.
I'm really surprised that SQL Server doesn't allow to disable
automatic creation of statistics on a per table basis. That could be
just really helpful in some cases.
|||Ok I think I've got an interesting workaround. As we cannot disable
autocreate statistics for a specific table, the idea is to update
those unwanted stats with two clauses :
- SAMPLE 0 ROWS : to empty the statistics, so that they don't
infuence the query optimizer anymore.
- NORECOMPUTE : to avoid the "auto update stats" option to repopulate
them later
Here is the SQL statement I wrote to do this automatically on SQL 2005
(you just need to set @.dbtname correctly). It's just necessary to run
it from time to time, to ensure that new autocreated stats are
disabled.
The first tests shows exactly the same performance compared to queries
with "auto create stats" disabled.
DECLARE @.dbtname NVARCHAR(255)
SET @.dbtname = 'You_Table_Name_Here'
DECLARE c CURSOR FOR
SELECT name FROM sys.stats WHERE object_id = object_id(@.dbtname) AND
auto_created = 1
DECLARE @.statname NVARCHAR(255)
OPEN c
FETCH next FROM c INTO @.statname
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT @.statname
EXEC ('UPDATE STATISTICS ' + @.dbtname + ' (' + @.statname + ') WITH
SAMPLE 0 ROWS, NORECOMPUTE')
FETCH NEXT FROM c INTO @.statname
END
CLOSE c
DEALLOCATE c
I'm experiencing a strange problem with query performance runing on
SQL2005. The database has 10+ tables, but we need to run really
specific queries in only 1 table with these caracteristics :
- 1 million rows
- we run everyday a few thousands queries on that table, each query
is unique (adhoc plan), and not parameterizable. (we cannot optimize
this)
- rows have a lot of nvarchar data
- all queries use a lot of LIKE / NOT LIKE statement (we cannot find
any work-around to that point, Fulltext is not adequate in that case)
- when LIKE operations are performed on columns, we always create a
duplicate column to optimize some search stuff, like putting
everything in Low Case, using Latin1_General_BIN collation, ...
- we have some indexes on short nvarchar columns, only those where we
use an exact '=' statemen
- we have another index on a float column
- all usefull indexes and statistics are manually created on that
table
- the nvarchar content of the table changes only once a day. It means
we do all optimization (indexes / stats) just after the update, and
there is no change on nvarchar data until the next update (24 hours
later)
I found that when "Auto Create Statistics" is enabled on the database,
that queries are really runing slower :
- "Auto Create Statistics" enabled : 57 min to run all queries
- "Auto Create Statistics" disabled and all auto-created stats
deleted : 7 min to run the same queries
It means that queries are running 8x slower when "Auto Create
Statistics" is enabled!
Another interesting point : just after disabling "Auto Create
Statistics", the queries continue to perform slowly until I manually
delete all statistics created automatically for that table (the one
begining with "_WA_Sys_"). It could mean that it's not a stat creation
issue, but only the existence of that statistics that could change the
query plan. But in both cases, the execution plan for the same query
seems to be exactly the same (same aspect, same costs). I also tried
to enable the Async stats update : no change.
The problem is that for all the other tables in the database, the
"Auto Create Statistics" is a good thing and useful. But not for that
specific table. Two questions :
- Is it possible to disable "Auto Create Statistics" on a specific
table? (I did not find anything about that in the BOL)
- If not, is there another work-around to deal with that kind of
performance drop?
Thanks.
We need the query plan with before and after to tell you why. It sounds like
there was an inaccurate estimate which might be fixed with a larger sample
than the default but that is a guess. SQL Server 2005 keeps better stats on
string column and it may be able to do a seek on a covering index in a LIKE
query especially with a larger sample. It just needs to be tested heavily.
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
<pinformaticien@.yahoo.fr> wrote in message
news:8384b744-0444-4f91-a1ce-b4d302317302@.13g2000hsb.googlegroups.com...
> Hello,
> I'm experiencing a strange problem with query performance runing on
> SQL2005. The database has 10+ tables, but we need to run really
> specific queries in only 1 table with these caracteristics :
> - 1 million rows
> - we run everyday a few thousands queries on that table, each query
> is unique (adhoc plan), and not parameterizable. (we cannot optimize
> this)
> - rows have a lot of nvarchar data
> - all queries use a lot of LIKE / NOT LIKE statement (we cannot find
> any work-around to that point, Fulltext is not adequate in that case)
> - when LIKE operations are performed on columns, we always create a
> duplicate column to optimize some search stuff, like putting
> everything in Low Case, using Latin1_General_BIN collation, ...
> - we have some indexes on short nvarchar columns, only those where we
> use an exact '=' statemen
> - we have another index on a float column
> - all usefull indexes and statistics are manually created on that
> table
> - the nvarchar content of the table changes only once a day. It means
> we do all optimization (indexes / stats) just after the update, and
> there is no change on nvarchar data until the next update (24 hours
> later)
> I found that when "Auto Create Statistics" is enabled on the database,
> that queries are really runing slower :
> - "Auto Create Statistics" enabled : 57 min to run all queries
> - "Auto Create Statistics" disabled and all auto-created stats
> deleted : 7 min to run the same queries
> It means that queries are running 8x slower when "Auto Create
> Statistics" is enabled!
> Another interesting point : just after disabling "Auto Create
> Statistics", the queries continue to perform slowly until I manually
> delete all statistics created automatically for that table (the one
> begining with "_WA_Sys_"). It could mean that it's not a stat creation
> issue, but only the existence of that statistics that could change the
> query plan. But in both cases, the execution plan for the same query
> seems to be exactly the same (same aspect, same costs). I also tried
> to enable the Async stats update : no change.
> The problem is that for all the other tables in the database, the
> "Auto Create Statistics" is a good thing and useful. But not for that
> specific table. Two questions :
> - Is it possible to disable "Auto Create Statistics" on a specific
> table? (I did not find anything about that in the BOL)
> - If not, is there another work-around to deal with that kind of
> performance drop?
> Thanks.
|||For what I've tried, creating then updating statistics on nvarchar
columns with the "WITH FULLSCAN" clause doesn't help. But here are
some interesting results : I setup a test server, and ran 2 times 10
queries, first time with "Auto Create Statistics" enabled, second time
with "Auto Create Statistics" disabled. Between the 2 tests, I deleted
all the automatically created statistics (the one begining with
"_WA_Sys_"), then restarted SQL server service. Here are the results
for the following query
Select * from sys.dm_exec_query_optimizer_info where counter in
('optimizations','elapsed time')
"Auto Create Statistics" enabled
optimizations 11 1
elapsed time 11 2,80751895306448
"Auto Create Statistics" disabled
optimizations 11 1
elapsed time 11 0,0665338534973798
It confirms that all the performance drop goes in optimization time
(2.8 sec average vs 0.07 sec), that finally almost doesn't otimize
anything in my case (it leads to the same execution plan is the same
is both cases). It means I need to find a way to disable / reduce that
optimization time when "Auto Create Statistics" is enabled. Any idea?
Is it possible to disable "Auto Create Statistics" on a specific
table?
|||It sounds like you are right. It sounds like optimizer is spending more time
try to compile since there are more options only to come up with the same
plan. You can disable autostats on a particular table with UPDATE STATISTICS
... WITH NORECOMPUTE.
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
<pinformaticien@.yahoo.fr> wrote in message
news:1e199977-1bc2-4614-ad72-036bb3111ce6@.d21g2000prf.googlegroups.com...
> For what I've tried, creating then updating statistics on nvarchar
> columns with the "WITH FULLSCAN" clause doesn't help. But here are
> some interesting results : I setup a test server, and ran 2 times 10
> queries, first time with "Auto Create Statistics" enabled, second time
> with "Auto Create Statistics" disabled. Between the 2 tests, I deleted
> all the automatically created statistics (the one begining with
> "_WA_Sys_"), then restarted SQL server service. Here are the results
> for the following query
> Select * from sys.dm_exec_query_optimizer_info where counter in
> ('optimizations','elapsed time')
> "Auto Create Statistics" enabled
> optimizations 11 1
> elapsed time 11 2,80751895306448
> "Auto Create Statistics" disabled
> optimizations 11 1
> elapsed time 11 0,0665338534973798
> It confirms that all the performance drop goes in optimization time
> (2.8 sec average vs 0.07 sec), that finally almost doesn't otimize
> anything in my case (it leads to the same execution plan is the same
> is both cases). It means I need to find a way to disable / reduce that
> optimization time when "Auto Create Statistics" is enabled. Any idea?
> Is it possible to disable "Auto Create Statistics" on a specific
> table?
|||Thanks for the reply. UPDATE STATISTICS ... WITH NORECOMPUTE would
just avoid statistics to be updated. In my case, it's not the stat
update which is problematic, but the existence of the automatically
created statistics (as they badly influence the query optimizer on
that table). One solution could be to move that table on a dedicated
database and turn "Auto Create Statistics" OFF, but we would like to
avoid this solution.
I'm really surprised that SQL Server doesn't allow to disable
automatic creation of statistics on a per table basis. That could be
just really helpful in some cases.
|||Ok I think I've got an interesting workaround. As we cannot disable
autocreate statistics for a specific table, the idea is to update
those unwanted stats with two clauses :
- SAMPLE 0 ROWS : to empty the statistics, so that they don't
infuence the query optimizer anymore.
- NORECOMPUTE : to avoid the "auto update stats" option to repopulate
them later
Here is the SQL statement I wrote to do this automatically on SQL 2005
(you just need to set @.dbtname correctly). It's just necessary to run
it from time to time, to ensure that new autocreated stats are
disabled.
The first tests shows exactly the same performance compared to queries
with "auto create stats" disabled.
DECLARE @.dbtname NVARCHAR(255)
SET @.dbtname = 'You_Table_Name_Here'
DECLARE c CURSOR FOR
SELECT name FROM sys.stats WHERE object_id = object_id(@.dbtname) AND
auto_created = 1
DECLARE @.statname NVARCHAR(255)
OPEN c
FETCH next FROM c INTO @.statname
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT @.statname
EXEC ('UPDATE STATISTICS ' + @.dbtname + ' (' + @.statname + ') WITH
SAMPLE 0 ROWS, NORECOMPUTE')
FETCH NEXT FROM c INTO @.statname
END
CLOSE c
DEALLOCATE c
Subscribe to:
Posts (Atom)
