I am working at a client site with an accounting database, and we are dealing with a simple query:
select count(*)
from Contract
inner join Charge on Contract.ContractID = Charge.ContractID
Oddly, when we run this on our production server it takes 60 to 90 seconds to complete, but on our test server it completes in 2 seconds.
The problem becomes worse with more complex procedures, such as our statement run that can exceed seven hours on the production server but completes in 45 minutes on our test server.
Our test server is an exact restore of our production server.
Contract and Charge are both indexed on ContractID, and each has about 500,000 rows.
The execution plans on both servers are identical, the longest step being a hash-match/Inner-join between the two tables (probably a result of the cardinality of ContractID in the Charge table).
Our production server is more powerful than our test server, with quad-processors and 3.5 gigs of RAM, while our test server has only 2 gigs. Both servers are set to dynamically configure memory, and I haven't seen our production server's memory use top 1.8 gigs.
Running trace through query analyzer shows one discrepancy: out test server performs only 24 reads to execute the query, while our productions server requires 220.
The problem occurs on our production server regardless of whether other users are using the system.
I have a couple questions:
1) Any ideas on why the query would run so much slower on our production server?
2) Would running SQL Profiler give me any additional information, and if so what settings should I trace (I've only used profiler once or twice). Also, is there anything I should be concerned about in running profiler on a production system? I've heard that it can have some impact on performance.
3) As part of the execution plan the optimizer performs a step called Bitmap/Bitmap Create prior to the hash. I can't find any documentation on this in Books Online or on Microsquashes website. Anybody know anything about it?
Thanks!
blindmanRunning trace through query analyzer shows one discrepancy: out test server performs only 24 reads to execute the query, while our productions server requires 220.
--------------
Check indexes for fragmentation.|||I tried dropping and recreating the indexes, to no effect.
I also just tried setting ContractID as the clustered index on the Charge table to see if that would change the execution plan, but it had no effect.
I am suspecting that there might be performance or contention issues with the drive, though supposedly there are no other services running on the serve besides MS SQL.
blindman|||Originally posted by blindman
I tried dropping and recreating the indexes, to no effect.
I also just tried setting ContractID as the clustered index on the Charge table to see if that would change the execution plan, but it had no effect.
I am suspecting that there might be performance or contention issues with the drive, though supposedly there are no other services running on the serve besides MS SQL.
blindman
Try to use performance monitor - very useful thing. Compare results for servers.|||uhhh...bounce the box?
This is MS BTW
Can you be down for a couple?
What's in the Error Log?
Did you do DBCC CHECKDB?|||blocking ?
Originally posted by blindman
I am working at a client site with an accounting database, and we are dealing with a simple query:
select count(*)
from Contract
inner join Charge on Contract.ContractID = Charge.ContractID
Oddly, when we run this on our production server it takes 60 to 90 seconds to complete, but on our test server it completes in 2 seconds.
The problem becomes worse with more complex procedures, such as our statement run that can exceed seven hours on the production server but completes in 45 minutes on our test server.
Our test server is an exact restore of our production server.
Contract and Charge are both indexed on ContractID, and each has about 500,000 rows.
The execution plans on both servers are identical, the longest step being a hash-match/Inner-join between the two tables (probably a result of the cardinality of ContractID in the Charge table).
Our production server is more powerful than our test server, with quad-processors and 3.5 gigs of RAM, while our test server has only 2 gigs. Both servers are set to dynamically configure memory, and I haven't seen our production server's memory use top 1.8 gigs.
Running trace through query analyzer shows one discrepancy: out test server performs only 24 reads to execute the query, while our productions server requires 220.
The problem occurs on our production server regardless of whether other users are using the system.
I have a couple questions:
1) Any ideas on why the query would run so much slower on our production server?
2) Would running SQL Profiler give me any additional information, and if so what settings should I trace (I've only used profiler once or twice). Also, is there anything I should be concerned about in running profiler on a production system? I've heard that it can have some impact on performance.
3) As part of the execution plan the optimizer performs a step called Bitmap/Bitmap Create prior to the hash. I can't find any documentation on this in Books Online or on Microsquashes website. Anybody know anything about it?
Thanks!
blindman|||Bouncing the server did speed up the processing the other day, but the performance quickly degraded again.
Problem with buffer pages, perhaps?
Nothing in the Error Log.
DBCC checks done regularly, and databases are spittin' images of eachother.
No blocks detected, and problem occurs regardless of whether other users are logged in.
blindman|||How big are the tranny logs?
How about the allocation to tempdb?
Is it a high level of OLTP?
But..
The execution plans on both servers are identical, the longest step being a hash-match/Inner-join between the two tables (probably a result of the cardinality of ContractID in the Charge table).
Should be an Index Scan...shouldn't it?
And 45 minutes is a long time....(7 hours is an eternity)...
Can it be the network? Are you executing localy on dev and remotley to PROD (even that shouldn't matter)
I'll keep thinking...
You can try the brain trust at:
http://www.sqlteam.com/Default.asp|||I know SQL Server is supposed to be doing this on its own every now and then, but what happens when you run
update statistics table with fullscan
on both tables? Kind of reaching here, because the test database is a restore of the other (and therefore identical). How does the hardware stack up?|||How does the hardware stack up?
...And if I read the REST of the post... Disregard that last bit, Blindman.
Showing posts with label client. Show all posts
Showing posts with label client. Show all posts
Friday, March 23, 2012
Wednesday, March 7, 2012
Query on 2 tables
Hello all,
If I write this query :
If I write this query :
SELECT [Client].[CLI_NAME], [Client].[CLI_PRENOM] FROM Client, Commandesthe return is good.
But If I the query is :
SELECT [Client].[CLI_NOM] & " " & [Commandes].[CMD_DATE_ORDER] As Ordered FROM Client, CommandesI have an error "Invalid column name.
The CLI_NOM field is on Client Table and CMD_DATE_ORDER is on the other Order table.
How to make a good query with an aliased column without error ?
Thanks for reply.
Regards.
PAB.Try changing this:
& " " &
to this:
& ' ' &|||Also the & should be replaced with +
hth|||Also the
&
should be replaced with
+.
hth|||Hello,
Thanks all. I try that solutions.
PAB.
Saturday, February 25, 2012
Query Notification & Windows Service
I could get Query Notification working for a windows forms client using the
SQL Dependency object but the same code doesn't work from a simple windows
service.
The OnChange event doesn't seem to be raised up to the windows service.
Looking at the SQL trace, it doesn't look like the a notification is sent to
the windows service from the SQL server.
Appreciate your response.
RamaThe notifications are sent using Service Broker, try following the steps in
this post http://blogs.msdn.com/remusrusanu/a.../20/506221.aspx
to figure out the cause.
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Rama" <rama.bhandaru@.eclipsys.com> wrote in message
news:en6X%23S$XGHA.1192@.TK2MSFTNGP03.phx.gbl...
>I could get Query Notification working for a windows forms client using the
> SQL Dependency object but the same code doesn't work from a simple windows
> service.
> The OnChange event doesn't seem to be raised up to the windows service.
> Looking at the SQL trace, it doesn't look like the a notification is sent
> to
> the windows service from the SQL server.
> Appreciate your response.
> Rama
>
SQL Dependency object but the same code doesn't work from a simple windows
service.
The OnChange event doesn't seem to be raised up to the windows service.
Looking at the SQL trace, it doesn't look like the a notification is sent to
the windows service from the SQL server.
Appreciate your response.
RamaThe notifications are sent using Service Broker, try following the steps in
this post http://blogs.msdn.com/remusrusanu/a.../20/506221.aspx
to figure out the cause.
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Rama" <rama.bhandaru@.eclipsys.com> wrote in message
news:en6X%23S$XGHA.1192@.TK2MSFTNGP03.phx.gbl...
>I could get Query Notification working for a windows forms client using the
> SQL Dependency object but the same code doesn't work from a simple windows
> service.
> The OnChange event doesn't seem to be raised up to the windows service.
> Looking at the SQL trace, it doesn't look like the a notification is sent
> to
> the windows service from the SQL server.
> Appreciate your response.
> Rama
>
Query not using index
Hi,
I have a client who is running a query (generated from his ERP system) that
is taking 2-3 minutes to run, doing a full table scan. I tried the same
thing in my database with the same database (tables etc.) and it uses an
index and takes a couple of seconds to run. I have checked that he has the
same index as me in place and he has re-indexed the table.
In addition he has Auto Create Statistics and Auto Update Statistics turned
on (same as me). I'm now running low on ideas. Can anybody give me a few
more pointers.
Thanks in advanceHi,
Execute DBCC SHOW_STATISTICS on that table and check when the statistics was
last updated.
As well as, Could you please run a DBCC DBREINDEX on that table and check
the query execution plan.
Thanks
Hari
MCDBA
"Michael Gill" <Michael Gill@.discussions.microsoft.com> wrote in message
news:CFC35F6A-D45C-4875-868F-25E9F21D2040@.microsoft.com...
> Hi,
> I have a client who is running a query (generated from his ERP system)
that
> is taking 2-3 minutes to run, doing a full table scan. I tried the same
> thing in my database with the same database (tables etc.) and it uses an
> index and takes a couple of seconds to run. I have checked that he has
the
> same index as me in place and he has re-indexed the table.
> In addition he has Auto Create Statistics and Auto Update Statistics
turned
> on (same as me). I'm now running low on ideas. Can anybody give me a few
> more pointers.
> Thanks in advance|||Michael Gill wrote:
> Hi,
> I have a client who is running a query (generated from his ERP
> system) that is taking 2-3 minutes to run, doing a full table scan.
> I tried the same thing in my database with the same database (tables
> etc.) and it uses an index and takes a couple of seconds to run. I
> have checked that he has the same index as me in place and he has
> re-indexed the table.
> In addition he has Auto Create Statistics and Auto Update Statistics
> turned on (same as me). I'm now running low on ideas. Can anybody
> give me a few more pointers.
> Thanks in advance
If you can try running DBCC DBREINDEX on this table. This is an offline
operation and the table will not be available during the rebuild.
As an alternative, you could try running DBCC INDEXDEFRAG on the table
which is an online operation.
Or you could try running Update Statistics on the table.
Using an index (or not) also depends on server utilization and memory,
table fragmentation, statistics, etc.
How many rows are returned from the query on the table where you expect
the index to be used? How many rows are in the table?
David G.|||Thanks for that Hari,
I got him to run the show_statistics command and it was last updated two
days ago. I asked him to run a DBREINDEX today when everyone is off the
system
Thanks for your help
Michael
"Hari Prasad" wrote:
> Hi,
> Execute DBCC SHOW_STATISTICS on that table and check when the statistics was
> last updated.
> As well as, Could you please run a DBCC DBREINDEX on that table and check
> the query execution plan.
>
> Thanks
> Hari
> MCDBA
>
> "Michael Gill" <Michael Gill@.discussions.microsoft.com> wrote in message
> news:CFC35F6A-D45C-4875-868F-25E9F21D2040@.microsoft.com...
> > Hi,
> >
> > I have a client who is running a query (generated from his ERP system)
> that
> > is taking 2-3 minutes to run, doing a full table scan. I tried the same
> > thing in my database with the same database (tables etc.) and it uses an
> > index and takes a couple of seconds to run. I have checked that he has
> the
> > same index as me in place and he has re-indexed the table.
> >
> > In addition he has Auto Create Statistics and Auto Update Statistics
> turned
> > on (same as me). I'm now running low on ideas. Can anybody give me a few
> > more pointers.
> >
> > Thanks in advance
>
>|||Hi David,
Thanks for your comments. The table contains 19,000 rows. The statistics
were last updated two days ago. I have asked him to run a dbcc reindex when
everyone is off the system tonight.
Michael|||Hi,
Since there is only 19,000 records you could execute DBCC INDEXDEFRAG during
online as David recommended.
Thanks
Hari
MCDBA
"Michael Gill" <Michael Gill@.discussions.microsoft.com> wrote in message
news:20DF83D2-A9FE-4E74-90CF-BC73E9991DA5@.microsoft.com...
> Hi David,
> Thanks for your comments. The table contains 19,000 rows. The statistics
> were last updated two days ago. I have asked him to run a dbcc reindex
when
> everyone is off the system tonight.
> Michael
>|||I want to give more infromation to you.
Though the value of rowmodctr(column of sysindexes table) is smaller than
the threashold that statistics is updated automatically on, some performance
problem can occur on your system because of huge table on AUTO UPDATE
STATISTICS.
Then you may update statistics periodically.
Hanky
"Michael Gill" <Michael Gill@.discussions.microsoft.com> wrote in message
news:CFC35F6A-D45C-4875-868F-25E9F21D2040@.microsoft.com...
> Hi,
> I have a client who is running a query (generated from his ERP system)
that
> is taking 2-3 minutes to run, doing a full table scan. I tried the same
> thing in my database with the same database (tables etc.) and it uses an
> index and takes a couple of seconds to run. I have checked that he has
the
> same index as me in place and he has re-indexed the table.
> In addition he has Auto Create Statistics and Auto Update Statistics
turned
> on (same as me). I'm now running low on ideas. Can anybody give me a few
> more pointers.
> Thanks in advance|||Need to view execution plans for both databases. BTW, check to see if the
servers have different parallism settings.
"Michael Gill" <Michael Gill@.discussions.microsoft.com> wrote in message
news:CFC35F6A-D45C-4875-868F-25E9F21D2040@.microsoft.com...
> Hi,
> I have a client who is running a query (generated from his ERP system)
that
> is taking 2-3 minutes to run, doing a full table scan. I tried the same
> thing in my database with the same database (tables etc.) and it uses an
> index and takes a couple of seconds to run. I have checked that he has
the
> same index as me in place and he has re-indexed the table.
> In addition he has Auto Create Statistics and Auto Update Statistics
turned
> on (same as me). I'm now running low on ideas. Can anybody give me a few
> more pointers.
> Thanks in advance|||If the 'slow' server is an SMP system, try disabling parallel processing
for that query by adding the OPTION (MAXDOP 1).
Gert-Jan
Michael Gill wrote:
> Hi,
> I have a client who is running a query (generated from his ERP system) that
> is taking 2-3 minutes to run, doing a full table scan. I tried the same
> thing in my database with the same database (tables etc.) and it uses an
> index and takes a couple of seconds to run. I have checked that he has the
> same index as me in place and he has re-indexed the table.
> In addition he has Auto Create Statistics and Auto Update Statistics turned
> on (same as me). I'm now running low on ideas. Can anybody give me a few
> more pointers.
> Thanks in advance
--
(Please reply only to the newsgroup)
I have a client who is running a query (generated from his ERP system) that
is taking 2-3 minutes to run, doing a full table scan. I tried the same
thing in my database with the same database (tables etc.) and it uses an
index and takes a couple of seconds to run. I have checked that he has the
same index as me in place and he has re-indexed the table.
In addition he has Auto Create Statistics and Auto Update Statistics turned
on (same as me). I'm now running low on ideas. Can anybody give me a few
more pointers.
Thanks in advanceHi,
Execute DBCC SHOW_STATISTICS on that table and check when the statistics was
last updated.
As well as, Could you please run a DBCC DBREINDEX on that table and check
the query execution plan.
Thanks
Hari
MCDBA
"Michael Gill" <Michael Gill@.discussions.microsoft.com> wrote in message
news:CFC35F6A-D45C-4875-868F-25E9F21D2040@.microsoft.com...
> Hi,
> I have a client who is running a query (generated from his ERP system)
that
> is taking 2-3 minutes to run, doing a full table scan. I tried the same
> thing in my database with the same database (tables etc.) and it uses an
> index and takes a couple of seconds to run. I have checked that he has
the
> same index as me in place and he has re-indexed the table.
> In addition he has Auto Create Statistics and Auto Update Statistics
turned
> on (same as me). I'm now running low on ideas. Can anybody give me a few
> more pointers.
> Thanks in advance|||Michael Gill wrote:
> Hi,
> I have a client who is running a query (generated from his ERP
> system) that is taking 2-3 minutes to run, doing a full table scan.
> I tried the same thing in my database with the same database (tables
> etc.) and it uses an index and takes a couple of seconds to run. I
> have checked that he has the same index as me in place and he has
> re-indexed the table.
> In addition he has Auto Create Statistics and Auto Update Statistics
> turned on (same as me). I'm now running low on ideas. Can anybody
> give me a few more pointers.
> Thanks in advance
If you can try running DBCC DBREINDEX on this table. This is an offline
operation and the table will not be available during the rebuild.
As an alternative, you could try running DBCC INDEXDEFRAG on the table
which is an online operation.
Or you could try running Update Statistics on the table.
Using an index (or not) also depends on server utilization and memory,
table fragmentation, statistics, etc.
How many rows are returned from the query on the table where you expect
the index to be used? How many rows are in the table?
David G.|||Thanks for that Hari,
I got him to run the show_statistics command and it was last updated two
days ago. I asked him to run a DBREINDEX today when everyone is off the
system
Thanks for your help
Michael
"Hari Prasad" wrote:
> Hi,
> Execute DBCC SHOW_STATISTICS on that table and check when the statistics was
> last updated.
> As well as, Could you please run a DBCC DBREINDEX on that table and check
> the query execution plan.
>
> Thanks
> Hari
> MCDBA
>
> "Michael Gill" <Michael Gill@.discussions.microsoft.com> wrote in message
> news:CFC35F6A-D45C-4875-868F-25E9F21D2040@.microsoft.com...
> > Hi,
> >
> > I have a client who is running a query (generated from his ERP system)
> that
> > is taking 2-3 minutes to run, doing a full table scan. I tried the same
> > thing in my database with the same database (tables etc.) and it uses an
> > index and takes a couple of seconds to run. I have checked that he has
> the
> > same index as me in place and he has re-indexed the table.
> >
> > In addition he has Auto Create Statistics and Auto Update Statistics
> turned
> > on (same as me). I'm now running low on ideas. Can anybody give me a few
> > more pointers.
> >
> > Thanks in advance
>
>|||Hi David,
Thanks for your comments. The table contains 19,000 rows. The statistics
were last updated two days ago. I have asked him to run a dbcc reindex when
everyone is off the system tonight.
Michael|||Hi,
Since there is only 19,000 records you could execute DBCC INDEXDEFRAG during
online as David recommended.
Thanks
Hari
MCDBA
"Michael Gill" <Michael Gill@.discussions.microsoft.com> wrote in message
news:20DF83D2-A9FE-4E74-90CF-BC73E9991DA5@.microsoft.com...
> Hi David,
> Thanks for your comments. The table contains 19,000 rows. The statistics
> were last updated two days ago. I have asked him to run a dbcc reindex
when
> everyone is off the system tonight.
> Michael
>|||I want to give more infromation to you.
Though the value of rowmodctr(column of sysindexes table) is smaller than
the threashold that statistics is updated automatically on, some performance
problem can occur on your system because of huge table on AUTO UPDATE
STATISTICS.
Then you may update statistics periodically.
Hanky
"Michael Gill" <Michael Gill@.discussions.microsoft.com> wrote in message
news:CFC35F6A-D45C-4875-868F-25E9F21D2040@.microsoft.com...
> Hi,
> I have a client who is running a query (generated from his ERP system)
that
> is taking 2-3 minutes to run, doing a full table scan. I tried the same
> thing in my database with the same database (tables etc.) and it uses an
> index and takes a couple of seconds to run. I have checked that he has
the
> same index as me in place and he has re-indexed the table.
> In addition he has Auto Create Statistics and Auto Update Statistics
turned
> on (same as me). I'm now running low on ideas. Can anybody give me a few
> more pointers.
> Thanks in advance|||Need to view execution plans for both databases. BTW, check to see if the
servers have different parallism settings.
"Michael Gill" <Michael Gill@.discussions.microsoft.com> wrote in message
news:CFC35F6A-D45C-4875-868F-25E9F21D2040@.microsoft.com...
> Hi,
> I have a client who is running a query (generated from his ERP system)
that
> is taking 2-3 minutes to run, doing a full table scan. I tried the same
> thing in my database with the same database (tables etc.) and it uses an
> index and takes a couple of seconds to run. I have checked that he has
the
> same index as me in place and he has re-indexed the table.
> In addition he has Auto Create Statistics and Auto Update Statistics
turned
> on (same as me). I'm now running low on ideas. Can anybody give me a few
> more pointers.
> Thanks in advance|||If the 'slow' server is an SMP system, try disabling parallel processing
for that query by adding the OPTION (MAXDOP 1).
Gert-Jan
Michael Gill wrote:
> Hi,
> I have a client who is running a query (generated from his ERP system) that
> is taking 2-3 minutes to run, doing a full table scan. I tried the same
> thing in my database with the same database (tables etc.) and it uses an
> index and takes a couple of seconds to run. I have checked that he has the
> same index as me in place and he has re-indexed the table.
> In addition he has Auto Create Statistics and Auto Update Statistics turned
> on (same as me). I'm now running low on ideas. Can anybody give me a few
> more pointers.
> Thanks in advance
--
(Please reply only to the newsgroup)
Query not using index
Hi,
I have a client who is running a query (generated from his ERP system) that
is taking 2-3 minutes to run, doing a full table scan. I tried the same
thing in my database with the same database (tables etc.) and it uses an
index and takes a couple of seconds to run. I have checked that he has the
same index as me in place and he has re-indexed the table.
In addition he has Auto Create Statistics and Auto Update Statistics turned
on (same as me). I'm now running low on ideas. Can anybody give me a few
more pointers.
Thanks in advanceHi,
Execute DBCC SHOW_STATISTICS on that table and check when the statistics was
last updated.
As well as, Could you please run a DBCC DBREINDEX on that table and check
the query execution plan.
Thanks
Hari
MCDBA
"Michael Gill" <Michael Gill@.discussions.microsoft.com> wrote in message
news:CFC35F6A-D45C-4875-868F-25E9F21D2040@.microsoft.com...
> Hi,
> I have a client who is running a query (generated from his ERP system)
that
> is taking 2-3 minutes to run, doing a full table scan. I tried the same
> thing in my database with the same database (tables etc.) and it uses an
> index and takes a couple of seconds to run. I have checked that he has
the
> same index as me in place and he has re-indexed the table.
> In addition he has Auto Create Statistics and Auto Update Statistics
turned
> on (same as me). I'm now running low on ideas. Can anybody give me a few
> more pointers.
> Thanks in advance|||Michael Gill wrote:
> Hi,
> I have a client who is running a query (generated from his ERP
> system) that is taking 2-3 minutes to run, doing a full table scan.
> I tried the same thing in my database with the same database (tables
> etc.) and it uses an index and takes a couple of seconds to run. I
> have checked that he has the same index as me in place and he has
> re-indexed the table.
> In addition he has Auto Create Statistics and Auto Update Statistics
> turned on (same as me). I'm now running low on ideas. Can anybody
> give me a few more pointers.
> Thanks in advance
If you can try running DBCC DBREINDEX on this table. This is an offline
operation and the table will not be available during the rebuild.
As an alternative, you could try running DBCC INDEXDEFRAG on the table
which is an online operation.
Or you could try running Update Statistics on the table.
Using an index (or not) also depends on server utilization and memory,
table fragmentation, statistics, etc.
How many rows are returned from the query on the table where you expect
the index to be used? How many rows are in the table?
David G.|||Thanks for that Hari,
I got him to run the show_statistics command and it was last updated two
days ago. I asked him to run a DBREINDEX today when everyone is off the
system
Thanks for your help
Michael
"Hari Prasad" wrote:
> Hi,
> Execute DBCC SHOW_STATISTICS on that table and check when the statistics w
as
> last updated.
> As well as, Could you please run a DBCC DBREINDEX on that table and check
> the query execution plan.
>
> Thanks
> Hari
> MCDBA
>
> "Michael Gill" <Michael Gill@.discussions.microsoft.com> wrote in message
> news:CFC35F6A-D45C-4875-868F-25E9F21D2040@.microsoft.com...
> that
> the
> turned
>
>|||Hi David,
Thanks for your comments. The table contains 19,000 rows. The statistics
were last updated two days ago. I have asked him to run a dbcc reindex when
everyone is off the system tonight.
Michael|||Hi,
Since there is only 19,000 records you could execute DBCC INDEXDEFRAG during
online as David recommended.
Thanks
Hari
MCDBA
"Michael Gill" <Michael Gill@.discussions.microsoft.com> wrote in message
news:20DF83D2-A9FE-4E74-90CF-BC73E9991DA5@.microsoft.com...
> Hi David,
> Thanks for your comments. The table contains 19,000 rows. The statistics
> were last updated two days ago. I have asked him to run a dbcc reindex
when
> everyone is off the system tonight.
> Michael
>|||I want to give more infromation to you.
Though the value of rowmodctr(column of sysindexes table) is smaller than
the threashold that statistics is updated automatically on, some performance
problem can occur on your system because of huge table on AUTO UPDATE
STATISTICS.
Then you may update statistics periodically.
Hanky
"Michael Gill" <Michael Gill@.discussions.microsoft.com> wrote in message
news:CFC35F6A-D45C-4875-868F-25E9F21D2040@.microsoft.com...
> Hi,
> I have a client who is running a query (generated from his ERP system)
that
> is taking 2-3 minutes to run, doing a full table scan. I tried the same
> thing in my database with the same database (tables etc.) and it uses an
> index and takes a couple of seconds to run. I have checked that he has
the
> same index as me in place and he has re-indexed the table.
> In addition he has Auto Create Statistics and Auto Update Statistics
turned
> on (same as me). I'm now running low on ideas. Can anybody give me a few
> more pointers.
> Thanks in advance|||Need to view execution plans for both databases. BTW, check to see if the
servers have different parallism settings.
"Michael Gill" <Michael Gill@.discussions.microsoft.com> wrote in message
news:CFC35F6A-D45C-4875-868F-25E9F21D2040@.microsoft.com...
> Hi,
> I have a client who is running a query (generated from his ERP system)
that
> is taking 2-3 minutes to run, doing a full table scan. I tried the same
> thing in my database with the same database (tables etc.) and it uses an
> index and takes a couple of seconds to run. I have checked that he has
the
> same index as me in place and he has re-indexed the table.
> In addition he has Auto Create Statistics and Auto Update Statistics
turned
> on (same as me). I'm now running low on ideas. Can anybody give me a few
> more pointers.
> Thanks in advance|||If the 'slow' server is an SMP system, try disabling parallel processing
for that query by adding the OPTION (MAXDOP 1).
Gert-Jan
Michael Gill wrote:
> Hi,
> I have a client who is running a query (generated from his ERP system) tha
t
> is taking 2-3 minutes to run, doing a full table scan. I tried the same
> thing in my database with the same database (tables etc.) and it uses an
> index and takes a couple of seconds to run. I have checked that he has th
e
> same index as me in place and he has re-indexed the table.
> In addition he has Auto Create Statistics and Auto Update Statistics turne
d
> on (same as me). I'm now running low on ideas. Can anybody give me a few
> more pointers.
> Thanks in advance
(Please reply only to the newsgroup)
I have a client who is running a query (generated from his ERP system) that
is taking 2-3 minutes to run, doing a full table scan. I tried the same
thing in my database with the same database (tables etc.) and it uses an
index and takes a couple of seconds to run. I have checked that he has the
same index as me in place and he has re-indexed the table.
In addition he has Auto Create Statistics and Auto Update Statistics turned
on (same as me). I'm now running low on ideas. Can anybody give me a few
more pointers.
Thanks in advanceHi,
Execute DBCC SHOW_STATISTICS on that table and check when the statistics was
last updated.
As well as, Could you please run a DBCC DBREINDEX on that table and check
the query execution plan.
Thanks
Hari
MCDBA
"Michael Gill" <Michael Gill@.discussions.microsoft.com> wrote in message
news:CFC35F6A-D45C-4875-868F-25E9F21D2040@.microsoft.com...
> Hi,
> I have a client who is running a query (generated from his ERP system)
that
> is taking 2-3 minutes to run, doing a full table scan. I tried the same
> thing in my database with the same database (tables etc.) and it uses an
> index and takes a couple of seconds to run. I have checked that he has
the
> same index as me in place and he has re-indexed the table.
> In addition he has Auto Create Statistics and Auto Update Statistics
turned
> on (same as me). I'm now running low on ideas. Can anybody give me a few
> more pointers.
> Thanks in advance|||Michael Gill wrote:
> Hi,
> I have a client who is running a query (generated from his ERP
> system) that is taking 2-3 minutes to run, doing a full table scan.
> I tried the same thing in my database with the same database (tables
> etc.) and it uses an index and takes a couple of seconds to run. I
> have checked that he has the same index as me in place and he has
> re-indexed the table.
> In addition he has Auto Create Statistics and Auto Update Statistics
> turned on (same as me). I'm now running low on ideas. Can anybody
> give me a few more pointers.
> Thanks in advance
If you can try running DBCC DBREINDEX on this table. This is an offline
operation and the table will not be available during the rebuild.
As an alternative, you could try running DBCC INDEXDEFRAG on the table
which is an online operation.
Or you could try running Update Statistics on the table.
Using an index (or not) also depends on server utilization and memory,
table fragmentation, statistics, etc.
How many rows are returned from the query on the table where you expect
the index to be used? How many rows are in the table?
David G.|||Thanks for that Hari,
I got him to run the show_statistics command and it was last updated two
days ago. I asked him to run a DBREINDEX today when everyone is off the
system
Thanks for your help
Michael
"Hari Prasad" wrote:
> Hi,
> Execute DBCC SHOW_STATISTICS on that table and check when the statistics w
as
> last updated.
> As well as, Could you please run a DBCC DBREINDEX on that table and check
> the query execution plan.
>
> Thanks
> Hari
> MCDBA
>
> "Michael Gill" <Michael Gill@.discussions.microsoft.com> wrote in message
> news:CFC35F6A-D45C-4875-868F-25E9F21D2040@.microsoft.com...
> that
> the
> turned
>
>|||Hi David,
Thanks for your comments. The table contains 19,000 rows. The statistics
were last updated two days ago. I have asked him to run a dbcc reindex when
everyone is off the system tonight.
Michael|||Hi,
Since there is only 19,000 records you could execute DBCC INDEXDEFRAG during
online as David recommended.
Thanks
Hari
MCDBA
"Michael Gill" <Michael Gill@.discussions.microsoft.com> wrote in message
news:20DF83D2-A9FE-4E74-90CF-BC73E9991DA5@.microsoft.com...
> Hi David,
> Thanks for your comments. The table contains 19,000 rows. The statistics
> were last updated two days ago. I have asked him to run a dbcc reindex
when
> everyone is off the system tonight.
> Michael
>|||I want to give more infromation to you.
Though the value of rowmodctr(column of sysindexes table) is smaller than
the threashold that statistics is updated automatically on, some performance
problem can occur on your system because of huge table on AUTO UPDATE
STATISTICS.
Then you may update statistics periodically.
Hanky
"Michael Gill" <Michael Gill@.discussions.microsoft.com> wrote in message
news:CFC35F6A-D45C-4875-868F-25E9F21D2040@.microsoft.com...
> Hi,
> I have a client who is running a query (generated from his ERP system)
that
> is taking 2-3 minutes to run, doing a full table scan. I tried the same
> thing in my database with the same database (tables etc.) and it uses an
> index and takes a couple of seconds to run. I have checked that he has
the
> same index as me in place and he has re-indexed the table.
> In addition he has Auto Create Statistics and Auto Update Statistics
turned
> on (same as me). I'm now running low on ideas. Can anybody give me a few
> more pointers.
> Thanks in advance|||Need to view execution plans for both databases. BTW, check to see if the
servers have different parallism settings.
"Michael Gill" <Michael Gill@.discussions.microsoft.com> wrote in message
news:CFC35F6A-D45C-4875-868F-25E9F21D2040@.microsoft.com...
> Hi,
> I have a client who is running a query (generated from his ERP system)
that
> is taking 2-3 minutes to run, doing a full table scan. I tried the same
> thing in my database with the same database (tables etc.) and it uses an
> index and takes a couple of seconds to run. I have checked that he has
the
> same index as me in place and he has re-indexed the table.
> In addition he has Auto Create Statistics and Auto Update Statistics
turned
> on (same as me). I'm now running low on ideas. Can anybody give me a few
> more pointers.
> Thanks in advance|||If the 'slow' server is an SMP system, try disabling parallel processing
for that query by adding the OPTION (MAXDOP 1).
Gert-Jan
Michael Gill wrote:
> Hi,
> I have a client who is running a query (generated from his ERP system) tha
t
> is taking 2-3 minutes to run, doing a full table scan. I tried the same
> thing in my database with the same database (tables etc.) and it uses an
> index and takes a couple of seconds to run. I have checked that he has th
e
> same index as me in place and he has re-indexed the table.
> In addition he has Auto Create Statistics and Auto Update Statistics turne
d
> on (same as me). I'm now running low on ideas. Can anybody give me a few
> more pointers.
> Thanks in advance
(Please reply only to the newsgroup)
Query not using index
Hi,
I have a client who is running a query (generated from his ERP system) that
is taking 2-3 minutes to run, doing a full table scan. I tried the same
thing in my database with the same database (tables etc.) and it uses an
index and takes a couple of seconds to run. I have checked that he has the
same index as me in place and he has re-indexed the table.
In addition he has Auto Create Statistics and Auto Update Statistics turned
on (same as me). I'm now running low on ideas. Can anybody give me a few
more pointers.
Thanks in advance
Hi,
Execute DBCC SHOW_STATISTICS on that table and check when the statistics was
last updated.
As well as, Could you please run a DBCC DBREINDEX on that table and check
the query execution plan.
Thanks
Hari
MCDBA
"Michael Gill" <Michael Gill@.discussions.microsoft.com> wrote in message
news:CFC35F6A-D45C-4875-868F-25E9F21D2040@.microsoft.com...
> Hi,
> I have a client who is running a query (generated from his ERP system)
that
> is taking 2-3 minutes to run, doing a full table scan. I tried the same
> thing in my database with the same database (tables etc.) and it uses an
> index and takes a couple of seconds to run. I have checked that he has
the
> same index as me in place and he has re-indexed the table.
> In addition he has Auto Create Statistics and Auto Update Statistics
turned
> on (same as me). I'm now running low on ideas. Can anybody give me a few
> more pointers.
> Thanks in advance
|||Michael Gill wrote:
> Hi,
> I have a client who is running a query (generated from his ERP
> system) that is taking 2-3 minutes to run, doing a full table scan.
> I tried the same thing in my database with the same database (tables
> etc.) and it uses an index and takes a couple of seconds to run. I
> have checked that he has the same index as me in place and he has
> re-indexed the table.
> In addition he has Auto Create Statistics and Auto Update Statistics
> turned on (same as me). I'm now running low on ideas. Can anybody
> give me a few more pointers.
> Thanks in advance
If you can try running DBCC DBREINDEX on this table. This is an offline
operation and the table will not be available during the rebuild.
As an alternative, you could try running DBCC INDEXDEFRAG on the table
which is an online operation.
Or you could try running Update Statistics on the table.
Using an index (or not) also depends on server utilization and memory,
table fragmentation, statistics, etc.
How many rows are returned from the query on the table where you expect
the index to be used? How many rows are in the table?
David G.
|||Thanks for that Hari,
I got him to run the show_statistics command and it was last updated two
days ago. I asked him to run a DBREINDEX today when everyone is off the
system
Thanks for your help
Michael
"Hari Prasad" wrote:
> Hi,
> Execute DBCC SHOW_STATISTICS on that table and check when the statistics was
> last updated.
> As well as, Could you please run a DBCC DBREINDEX on that table and check
> the query execution plan.
>
> Thanks
> Hari
> MCDBA
>
> "Michael Gill" <Michael Gill@.discussions.microsoft.com> wrote in message
> news:CFC35F6A-D45C-4875-868F-25E9F21D2040@.microsoft.com...
> that
> the
> turned
>
>
|||Hi David,
Thanks for your comments. The table contains 19,000 rows. The statistics
were last updated two days ago. I have asked him to run a dbcc reindex when
everyone is off the system tonight.
Michael
|||Hi,
Since there is only 19,000 records you could execute DBCC INDEXDEFRAG during
online as David recommended.
Thanks
Hari
MCDBA
"Michael Gill" <Michael Gill@.discussions.microsoft.com> wrote in message
news:20DF83D2-A9FE-4E74-90CF-BC73E9991DA5@.microsoft.com...
> Hi David,
> Thanks for your comments. The table contains 19,000 rows. The statistics
> were last updated two days ago. I have asked him to run a dbcc reindex
when
> everyone is off the system tonight.
> Michael
>
|||I want to give more infromation to you.
Though the value of rowmodctr(column of sysindexes table) is smaller than
the threashold that statistics is updated automatically on, some performance
problem can occur on your system because of huge table on AUTO UPDATE
STATISTICS.
Then you may update statistics periodically.
Hanky
"Michael Gill" <Michael Gill@.discussions.microsoft.com> wrote in message
news:CFC35F6A-D45C-4875-868F-25E9F21D2040@.microsoft.com...
> Hi,
> I have a client who is running a query (generated from his ERP system)
that
> is taking 2-3 minutes to run, doing a full table scan. I tried the same
> thing in my database with the same database (tables etc.) and it uses an
> index and takes a couple of seconds to run. I have checked that he has
the
> same index as me in place and he has re-indexed the table.
> In addition he has Auto Create Statistics and Auto Update Statistics
turned
> on (same as me). I'm now running low on ideas. Can anybody give me a few
> more pointers.
> Thanks in advance
|||Need to view execution plans for both databases. BTW, check to see if the
servers have different parallism settings.
"Michael Gill" <Michael Gill@.discussions.microsoft.com> wrote in message
news:CFC35F6A-D45C-4875-868F-25E9F21D2040@.microsoft.com...
> Hi,
> I have a client who is running a query (generated from his ERP system)
that
> is taking 2-3 minutes to run, doing a full table scan. I tried the same
> thing in my database with the same database (tables etc.) and it uses an
> index and takes a couple of seconds to run. I have checked that he has
the
> same index as me in place and he has re-indexed the table.
> In addition he has Auto Create Statistics and Auto Update Statistics
turned
> on (same as me). I'm now running low on ideas. Can anybody give me a few
> more pointers.
> Thanks in advance
|||If the 'slow' server is an SMP system, try disabling parallel processing
for that query by adding the OPTION (MAXDOP 1).
Gert-Jan
Michael Gill wrote:
> Hi,
> I have a client who is running a query (generated from his ERP system) that
> is taking 2-3 minutes to run, doing a full table scan. I tried the same
> thing in my database with the same database (tables etc.) and it uses an
> index and takes a couple of seconds to run. I have checked that he has the
> same index as me in place and he has re-indexed the table.
> In addition he has Auto Create Statistics and Auto Update Statistics turned
> on (same as me). I'm now running low on ideas. Can anybody give me a few
> more pointers.
> Thanks in advance
(Please reply only to the newsgroup)
I have a client who is running a query (generated from his ERP system) that
is taking 2-3 minutes to run, doing a full table scan. I tried the same
thing in my database with the same database (tables etc.) and it uses an
index and takes a couple of seconds to run. I have checked that he has the
same index as me in place and he has re-indexed the table.
In addition he has Auto Create Statistics and Auto Update Statistics turned
on (same as me). I'm now running low on ideas. Can anybody give me a few
more pointers.
Thanks in advance
Hi,
Execute DBCC SHOW_STATISTICS on that table and check when the statistics was
last updated.
As well as, Could you please run a DBCC DBREINDEX on that table and check
the query execution plan.
Thanks
Hari
MCDBA
"Michael Gill" <Michael Gill@.discussions.microsoft.com> wrote in message
news:CFC35F6A-D45C-4875-868F-25E9F21D2040@.microsoft.com...
> Hi,
> I have a client who is running a query (generated from his ERP system)
that
> is taking 2-3 minutes to run, doing a full table scan. I tried the same
> thing in my database with the same database (tables etc.) and it uses an
> index and takes a couple of seconds to run. I have checked that he has
the
> same index as me in place and he has re-indexed the table.
> In addition he has Auto Create Statistics and Auto Update Statistics
turned
> on (same as me). I'm now running low on ideas. Can anybody give me a few
> more pointers.
> Thanks in advance
|||Michael Gill wrote:
> Hi,
> I have a client who is running a query (generated from his ERP
> system) that is taking 2-3 minutes to run, doing a full table scan.
> I tried the same thing in my database with the same database (tables
> etc.) and it uses an index and takes a couple of seconds to run. I
> have checked that he has the same index as me in place and he has
> re-indexed the table.
> In addition he has Auto Create Statistics and Auto Update Statistics
> turned on (same as me). I'm now running low on ideas. Can anybody
> give me a few more pointers.
> Thanks in advance
If you can try running DBCC DBREINDEX on this table. This is an offline
operation and the table will not be available during the rebuild.
As an alternative, you could try running DBCC INDEXDEFRAG on the table
which is an online operation.
Or you could try running Update Statistics on the table.
Using an index (or not) also depends on server utilization and memory,
table fragmentation, statistics, etc.
How many rows are returned from the query on the table where you expect
the index to be used? How many rows are in the table?
David G.
|||Thanks for that Hari,
I got him to run the show_statistics command and it was last updated two
days ago. I asked him to run a DBREINDEX today when everyone is off the
system
Thanks for your help
Michael
"Hari Prasad" wrote:
> Hi,
> Execute DBCC SHOW_STATISTICS on that table and check when the statistics was
> last updated.
> As well as, Could you please run a DBCC DBREINDEX on that table and check
> the query execution plan.
>
> Thanks
> Hari
> MCDBA
>
> "Michael Gill" <Michael Gill@.discussions.microsoft.com> wrote in message
> news:CFC35F6A-D45C-4875-868F-25E9F21D2040@.microsoft.com...
> that
> the
> turned
>
>
|||Hi David,
Thanks for your comments. The table contains 19,000 rows. The statistics
were last updated two days ago. I have asked him to run a dbcc reindex when
everyone is off the system tonight.
Michael
|||Hi,
Since there is only 19,000 records you could execute DBCC INDEXDEFRAG during
online as David recommended.
Thanks
Hari
MCDBA
"Michael Gill" <Michael Gill@.discussions.microsoft.com> wrote in message
news:20DF83D2-A9FE-4E74-90CF-BC73E9991DA5@.microsoft.com...
> Hi David,
> Thanks for your comments. The table contains 19,000 rows. The statistics
> were last updated two days ago. I have asked him to run a dbcc reindex
when
> everyone is off the system tonight.
> Michael
>
|||I want to give more infromation to you.
Though the value of rowmodctr(column of sysindexes table) is smaller than
the threashold that statistics is updated automatically on, some performance
problem can occur on your system because of huge table on AUTO UPDATE
STATISTICS.
Then you may update statistics periodically.
Hanky
"Michael Gill" <Michael Gill@.discussions.microsoft.com> wrote in message
news:CFC35F6A-D45C-4875-868F-25E9F21D2040@.microsoft.com...
> Hi,
> I have a client who is running a query (generated from his ERP system)
that
> is taking 2-3 minutes to run, doing a full table scan. I tried the same
> thing in my database with the same database (tables etc.) and it uses an
> index and takes a couple of seconds to run. I have checked that he has
the
> same index as me in place and he has re-indexed the table.
> In addition he has Auto Create Statistics and Auto Update Statistics
turned
> on (same as me). I'm now running low on ideas. Can anybody give me a few
> more pointers.
> Thanks in advance
|||Need to view execution plans for both databases. BTW, check to see if the
servers have different parallism settings.
"Michael Gill" <Michael Gill@.discussions.microsoft.com> wrote in message
news:CFC35F6A-D45C-4875-868F-25E9F21D2040@.microsoft.com...
> Hi,
> I have a client who is running a query (generated from his ERP system)
that
> is taking 2-3 minutes to run, doing a full table scan. I tried the same
> thing in my database with the same database (tables etc.) and it uses an
> index and takes a couple of seconds to run. I have checked that he has
the
> same index as me in place and he has re-indexed the table.
> In addition he has Auto Create Statistics and Auto Update Statistics
turned
> on (same as me). I'm now running low on ideas. Can anybody give me a few
> more pointers.
> Thanks in advance
|||If the 'slow' server is an SMP system, try disabling parallel processing
for that query by adding the OPTION (MAXDOP 1).
Gert-Jan
Michael Gill wrote:
> Hi,
> I have a client who is running a query (generated from his ERP system) that
> is taking 2-3 minutes to run, doing a full table scan. I tried the same
> thing in my database with the same database (tables etc.) and it uses an
> index and takes a couple of seconds to run. I have checked that he has the
> same index as me in place and he has re-indexed the table.
> In addition he has Auto Create Statistics and Auto Update Statistics turned
> on (same as me). I'm now running low on ideas. Can anybody give me a few
> more pointers.
> Thanks in advance
(Please reply only to the newsgroup)
Subscribe to:
Posts (Atom)