Query Plans differs

Why shoudl the query plan derived by an SP and one on Query Analyser would
differ widely?
I have a system that has been running stably for a while now when one of the
queries started behaving badly. The SP version of the query comes out with
an estimated cost of 0.311 whereas if I take the same TSQL and paste it into
Query Analyser it estimates as 0.153.
Unfortuately, the query plan cost isn't the whole story as when estimated
the SP performs about 5m page reads whereas the explicit code only takes 800
page reads - this makes a big difference in execution time :-)
Any ideas how to solve this - I've tried WITH RECOMPILE on the SP, but it
doesn't effect the QP choosen.
Regards
PaulPaul,
The two pieces of code, TSQL script and stored procedure, are not optimized
the same because of the knowledge available at the time of optimization.
For example, the TSQL script has the variables set with the values that you
want to run.
Here is one thing that causes stored procedures to be less than optimal:
If internally to a stored proc you set a variable, the optimizer (which has
no value for the variable when it optimizes) may have selected a suboptimal
plan. So, if this is your case, rather than:
DECLARE @.MyVal INT
SET @.MyVal = 123
SELECT ... WHERE MyCol = @.MyVal
You could do:
DECLARE @.MyVal INT
SET @.MyVal = 123
EXEC AnotherStoredProcedure @.MyVal
The called stored procedure will optimize with the value supplied in @.MyVal.
Remember, of course, that recompiles of stored procedures are common, so
this is not as simple as black and white.
And, of course, there are other factors that could mislead the optimizer.
RLF
"Paul Hatcher" <phatcher@.spamless.cix.co.uk> wrote in message
news:uU1mWm$HFHA.1580@.TK2MSFTNGP10.phx.gbl...
> Why shoudl the query plan derived by an SP and one on Query Analyser would
> differ widely?
> I have a system that has been running stably for a while now when one of
> the
> queries started behaving badly. The SP version of the query comes out with
> an estimated cost of 0.311 whereas if I take the same TSQL and paste it
> into
> Query Analyser it estimates as 0.153.
> Unfortuately, the query plan cost isn't the whole story as when estimated
> the SP performs about 5m page reads whereas the explicit code only takes
> 800
> page reads - this makes a big difference in execution time :-)
> Any ideas how to solve this - I've tried WITH RECOMPILE on the SP, but it
> doesn't effect the QP choosen.
> Regards
> Paul
>|||Thanks, there was one such variable, so I changed the code as per your
suggestion - reduced it from 5m reads to around 4.5m :-)
The optimiser normally does such a good job that it's just a pain when it
gets it wrong. Given the nature of this query, it's almost impossible to put
any useful hints that wouldn't cause much more pain elsewhere in the system.
I'll have to see if I can program around it with a different query just to
get it back running - previously it took around 100ms now it takes 10s+
Paul
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:#u#Rlw$HFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Paul,
> The two pieces of code, TSQL script and stored procedure, are not
optimized
> the same because of the knowledge available at the time of optimization.
> For example, the TSQL script has the variables set with the values that
you
> want to run.
> Here is one thing that causes stored procedures to be less than optimal:
> If internally to a stored proc you set a variable, the optimizer (which
has
> no value for the variable when it optimizes) may have selected a
suboptimal
> plan. So, if this is your case, rather than:
> DECLARE @.MyVal INT
> SET @.MyVal = 123
> SELECT ... WHERE MyCol = @.MyVal
> You could do:
> DECLARE @.MyVal INT
> SET @.MyVal = 123
> EXEC AnotherStoredProcedure @.MyVal
> The called stored procedure will optimize with the value supplied in
@.MyVal.
> Remember, of course, that recompiles of stored procedures are common, so
> this is not as simple as black and white.
> And, of course, there are other factors that could mislead the optimizer.
> RLF
> "Paul Hatcher" <phatcher@.spamless.cix.co.uk> wrote in message
> news:uU1mWm$HFHA.1580@.TK2MSFTNGP10.phx.gbl...
would
with
estimated
it
>|||Paul,
Maybe if you post the query in question (accompanied with the relevant
DDL) someone might be able to give a more specific advice...
Gert-Jan
Paul Hatcher wrote:
> Thanks, there was one such variable, so I changed the code as per your
> suggestion - reduced it from 5m reads to around 4.5m :-)
> The optimiser normally does such a good job that it's just a pain when it
> gets it wrong. Given the nature of this query, it's almost impossible to p
ut
> any useful hints that wouldn't cause much more pain elsewhere in the syste
m.
> I'll have to see if I can program around it with a different query just to
> get it back running - previously it took around 100ms now it takes 10s+
> Paul
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:#u#Rlw$HFHA.2924@.TK2MSFTNGP15.phx.gbl...
> optimized
> you
> has
> suboptimal
> @.MyVal.
> would
> with
> estimated
> it|||Gert-Jan
Thanks for the offer, but it's too complicated - as an example I would guess
that the query pans out to about a 30 table join, mostly self-joins as it's
a triple-based schema, but still fairly hairy.
I've now managed to solve it by a different structured query.
Paul
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:422766C1.A0FE26A1@.toomuchspamalready.nl...
> Paul,
> Maybe if you post the query in question (accompanied with the relevant
> DDL) someone might be able to give a more specific advice...
> Gert-Jan
>
> Paul Hatcher wrote:
it
put
system.
to
optimization.
that
optimal:
(which
so
optimizer.
one of
out
it
takes
butsql

Query plan miscalculates row count causing system slow down?

Has anyone seem this before. I've actually caught it once but I think
it's happening multiple times. I have a partitioned view and
performance is generally really good. However, every once in a great
while we'll see a stored procedure "hang" and just run for hours.
Doing much digging the only abnormalities I found where these: 1) the
server was requesting a large amount of PAGE and KEY locks (meaning an
index lock was requested. Checking the locks for that SPID confirmed
this) 2) At one time during testing I saw that the execution plan
called for an estimated 1 billion rows to be returned. Amazing since
the underlying table only have 400,000 records. Now, the theory is
this: Since I'm using a partitioned view and Microsoft claims that
they retain no histogram (statistical data from which to build an
"intelligent" execution plan) somehow it's figuring this 1 billion
estimated row count again and is entering some kind of horrendous
loop. Any thoughts?Have you checked the fragentation of the indexes on the table(s) that the sp
uses?
dbcc showcontig ('table_name').
If the scan desity is low, you might want to do a defrag.
dbcc indexdefrag ('database_name','table_name','index_name')
or in 2005
alter index <index_name> | ALL
rebuild with online = ON
on <table_name>
Sometimes rebuilding is better because is certain cases, indexdefrag doesn't
make the all of index pages contiguous.
--
MG
"tmorris" wrote:
> Has anyone seem this before. I've actually caught it once but I think
> it's happening multiple times. I have a partitioned view and
> performance is generally really good. However, every once in a great
> while we'll see a stored procedure "hang" and just run for hours.
> Doing much digging the only abnormalities I found where these: 1) the
> server was requesting a large amount of PAGE and KEY locks (meaning an
> index lock was requested. Checking the locks for that SPID confirmed
> this) 2) At one time during testing I saw that the execution plan
> called for an estimated 1 billion rows to be returned. Amazing since
> the underlying table only have 400,000 records. Now, the theory is
> this: Since I'm using a partitioned view and Microsoft claims that
> they retain no histogram (statistical data from which to build an
> "intelligent" execution plan) somehow it's figuring this 1 billion
> estimated row count again and is entering some kind of horrendous
> loop. Any thoughts?
>|||Look in BOL for update statistics
--
TheSQLGuru
President
Indicium Resources, Inc.
"tmorris" <TheRealPawn@.gmail.com> wrote in message
news:1177340474.292787.166810@.q75g2000hsh.googlegroups.com...
> Has anyone seem this before. I've actually caught it once but I think
> it's happening multiple times. I have a partitioned view and
> performance is generally really good. However, every once in a great
> while we'll see a stored procedure "hang" and just run for hours.
> Doing much digging the only abnormalities I found where these: 1) the
> server was requesting a large amount of PAGE and KEY locks (meaning an
> index lock was requested. Checking the locks for that SPID confirmed
> this) 2) At one time during testing I saw that the execution plan
> called for an estimated 1 billion rows to be returned. Amazing since
> the underlying table only have 400,000 records. Now, the theory is
> this: Since I'm using a partitioned view and Microsoft claims that
> they retain no histogram (statistical data from which to build an
> "intelligent" execution plan) somehow it's figuring this 1 billion
> estimated row count again and is entering some kind of horrendous
> loop. Any thoughts?
>|||DBCC dbreindex is preformed on all tables once a week as a form of
matanance.
We've concided doing this again midweek or one of the other processes
that update statistics on columns and indexes in SQL 2000. However, in
the test enviroment it doesn't seem to have any impact one way or
another. Like I, and microsoft said, partitioned views don't contain
any statistical history from which to base an exection plan. Thanks
for the suggestion though. I'm looking more of someone to confirm my
theory or say it's complete bunk.
Now, the theory is this: Since I'm using a partitioned view and
Microsoft claims that they retain no histogram (statistical data from
which to build an "intelligent" execution plan) somehow it's figuring
this 1 billion estimated row count again and is entering some kind of
horrendous loop. Any thoughts?
On Apr 23, 11:26 am, Hurme <michael.ge...@.thomson.com> wrote:
> Have you checked the fragentation of the indexes on the table(s) that the sp
> uses?
> dbcc showcontig ('table_name').
> If the scan desity is low, you might want to do a defrag.
> dbcc indexdefrag ('database_name','table_name','index_name')
> or in 2005
> alter index <index_name> | ALL
> rebuild with online = ON
> on <table_name>
> Sometimes rebuilding is better because is certain cases, indexdefrag doesn't
> make the all of index pages contiguous.
> --
> MG
> "tmorris" wrote:
> > Has anyone seem this before. I've actually caught it once but I think
> > it's happening multiple times. I have a partitioned view and
> > performance is generally really good. However, every once in a great
> > while we'll see a stored procedure "hang" and just run for hours.
> > Doing much digging the only abnormalities I found where these: 1) the
> > server was requesting a large amount of PAGE and KEY locks (meaning an
> > index lock was requested. Checking the locks for that SPID confirmed
> > this) 2) At one time during testing I saw that the execution plan
> > called for an estimated 1 billion rows to be returned. Amazing since
> > the underlying table only have 400,000 records. Now, the theory is
> > this: Since I'm using a partitioned view and Microsoft claims that
> > they retain no histogram (statistical data from which to build an
> > "intelligent" execution plan) somehow it's figuring this 1 billion
> > estimated row count again and is entering some kind of horrendous
> > loop. Any thoughts?|||We Run DBCC ReIndex on all tables as a weekly maintenance job. And we
looked as doing update statistics 25% midweek but it seemed to have no
effect in our test environment
On Apr 23, 12:42 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> Look in BOL for update statistics
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "tmorris" <TheRealP...@.gmail.com> wrote in message
> news:1177340474.292787.166810@.q75g2000hsh.googlegroups.com...
> > Has anyone seem this before. I've actually caught it once but I think
> > it's happening multiple times. I have a partitioned view and
> > performance is generally really good. However, every once in a great
> > while we'll see a stored procedure "hang" and just run for hours.
> > Doing much digging the only abnormalities I found where these: 1) the
> > server was requesting a large amount of PAGE and KEY locks (meaning an
> > index lock was requested. Checking the locks for that SPID confirmed
> > this) 2) At one time during testing I saw that the execution plan
> > called for an estimated 1 billion rows to be returned. Amazing since
> > the underlying table only have 400,000 records. Now, the theory is
> > this: Since I'm using a partitioned view and Microsoft claims that
> > they retain no histogram (statistical data from which to build an
> > "intelligent" execution plan) somehow it's figuring this 1 billion
> > estimated row count again and is entering some kind of horrendous
> > loop. Any thoughts?

Query plan miscalculates row count causing system slow down?

Has anyone seem this before. I've actually caught it once but I think
it's happening multiple times. I have a partitioned view and
performance is generally really good. However, every once in a great
while we'll see a stored procedure "hang" and just run for hours.
Doing much digging the only abnormalities I found where these: 1) the
server was requesting a large amount of PAGE and KEY locks (meaning an
index lock was requested. Checking the locks for that SPID confirmed
this) 2) At one time during testing I saw that the execution plan
called for an estimated 1 billion rows to be returned. Amazing since
the underlying table only have 400,000 records. Now, the theory is
this: Since I'm using a partitioned view and Microsoft claims that
they retain no histogram (statistical data from which to build an
"intelligent" execution plan) somehow it's figuring this 1 billion
estimated row count again and is entering some kind of horrendous
loop. Any thoughts?Have you checked the fragentation of the indexes on the table(s) that the sp
uses?
dbcc showcontig ('table_name').
If the scan desity is low, you might want to do a defrag.
dbcc indexdefrag ('database_name','table_name','index_nam
e')
or in 2005
alter index <index_name> | ALL
rebuild with online = ON
on <table_name>
Sometimes rebuilding is better because is certain cases, indexdefrag doesn't
make the all of index pages contiguous.
--
MG
"tmorris" wrote:

> Has anyone seem this before. I've actually caught it once but I think
> it's happening multiple times. I have a partitioned view and
> performance is generally really good. However, every once in a great
> while we'll see a stored procedure "hang" and just run for hours.
> Doing much digging the only abnormalities I found where these: 1) the
> server was requesting a large amount of PAGE and KEY locks (meaning an
> index lock was requested. Checking the locks for that SPID confirmed
> this) 2) At one time during testing I saw that the execution plan
> called for an estimated 1 billion rows to be returned. Amazing since
> the underlying table only have 400,000 records. Now, the theory is
> this: Since I'm using a partitioned view and Microsoft claims that
> they retain no histogram (statistical data from which to build an
> "intelligent" execution plan) somehow it's figuring this 1 billion
> estimated row count again and is entering some kind of horrendous
> loop. Any thoughts?
>|||Look in BOL for update statistics
TheSQLGuru
President
Indicium Resources, Inc.
"tmorris" <TheRealPawn@.gmail.com> wrote in message
news:1177340474.292787.166810@.q75g2000hsh.googlegroups.com...
> Has anyone seem this before. I've actually caught it once but I think
> it's happening multiple times. I have a partitioned view and
> performance is generally really good. However, every once in a great
> while we'll see a stored procedure "hang" and just run for hours.
> Doing much digging the only abnormalities I found where these: 1) the
> server was requesting a large amount of PAGE and KEY locks (meaning an
> index lock was requested. Checking the locks for that SPID confirmed
> this) 2) At one time during testing I saw that the execution plan
> called for an estimated 1 billion rows to be returned. Amazing since
> the underlying table only have 400,000 records. Now, the theory is
> this: Since I'm using a partitioned view and Microsoft claims that
> they retain no histogram (statistical data from which to build an
> "intelligent" execution plan) somehow it's figuring this 1 billion
> estimated row count again and is entering some kind of horrendous
> loop. Any thoughts?
>|||DBCC dbreindex is preformed on all tables once a week as a form of
matanance.
We've concided doing this again midweek or one of the other processes
that update statistics on columns and indexes in SQL 2000. However, in
the test enviroment it doesn't seem to have any impact one way or
another. Like I, and microsoft said, partitioned views don't contain
any statistical history from which to base an exection plan. Thanks
for the suggestion though. I'm looking more of someone to confirm my
theory or say it's complete bunk.
Now, the theory is this: Since I'm using a partitioned view and
Microsoft claims that they retain no histogram (statistical data from
which to build an "intelligent" execution plan) somehow it's figuring
this 1 billion estimated row count again and is entering some kind of
horrendous loop. Any thoughts?
On Apr 23, 11:26 am, Hurme <michael.ge...@.thomson.com> wrote:[vbcol=seagreen]
> Have you checked the fragentation of the indexes on the table(s) that the
sp
> uses?
> dbcc showcontig ('table_name').
> If the scan desity is low, you might want to do a defrag.
> dbcc indexdefrag ('database_name','table_name','index_nam
e')
> or in 2005
> alter index <index_name> | ALL
> rebuild with online = ON
> on <table_name>
> Sometimes rebuilding is better because is certain cases, indexdefrag doesn
't
> make the all of index pages contiguous.
> --
> MG
> "tmorris" wrote:|||We Run DBCC ReIndex on all tables as a weekly maintenance job. And we
looked as doing update statistics 25% midweek but it seemed to have no
effect in our test environment
On Apr 23, 12:42 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:[vbcol=seagreen]
> Look in BOL for update statistics
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "tmorris" <TheRealP...@.gmail.com> wrote in message
> news:1177340474.292787.166810@.q75g2000hsh.googlegroups.com...
>

Query optimization question...

I'm trying to optimize some queries on an existing system, and I'm
noticing some odd behavior. I'm performing a join between several
tables, the final table being joined by the optimizer has a clustered
index on the field that it is using to join to the rest of the query,
but for some reason SQL Server doesn't seem to actually use this index
(it's doing an index scan instead of an index seek). Is there some
reason why SQL Server would not use a valid Clustered Index? I've
dropped and readded the index, but that doesn't seem to help. I don't
know if it would be relevant, but the tables I'm working on are fairly
fat (2 to 7K bytes/row).

This is happening for several tables. I've been able to get around it
for some of the tables by creating a non-clustered index on all the
fields that are being queried so that the leaf pages don't need to be
loaded, but this isn't a valid solution for all of the tables I'm
struggling with.

Any ideas? (and no, they aren't willing to redesign any of the
tables)."Mathew Relick" <ticars@.yahoo.com> wrote in message
news:dd84d8a7.0311181017.39d1c69@.posting.google.co m...
> I'm trying to optimize some queries on an existing system, and I'm
> noticing some odd behavior. I'm performing a join between several
> tables, the final table being joined by the optimizer has a clustered
> index on the field that it is using to join to the rest of the query,
> but for some reason SQL Server doesn't seem to actually use this index
> (it's doing an index scan instead of an index seek). Is there some
> reason why SQL Server would not use a valid Clustered Index? I've
> dropped and readded the index, but that doesn't seem to help. I don't

This can happen if your table statistics are out of date. If SQL Server
determines that the join will require more than x % of the table to be
retrieved, then doing a clustered index scan may be faster than doing a
clustered index seek (faster because an index scan can read the rows in a
page sequentially, whereas an index seek has to traverse the B-tree
structure. Sequential read is faster because you cut down on seek time, and
also because you may be able to read more than one page in a single I/O
operation, since the data is sequential.) Try updating statistics and see
if it helps:

UPDATE STATISTICS <table_name> WITH FULLSCAN
GO

> know if it would be relevant, but the tables I'm working on are fairly
> fat (2 to 7K bytes/row).
> This is happening for several tables. I've been able to get around it
> for some of the tables by creating a non-clustered index on all the
> fields that are being queried so that the leaf pages don't need to be
> loaded, but this isn't a valid solution for all of the tables I'm
> struggling with.

This is because when you create a new index, new statistics are generated
for that index, so you have the most up-to-date statistics with your new
index. Same if you rebuild your existing indexes.

HTH,
Dave

>
> Any ideas? (and no, they aren't willing to redesign any of the
> tables).|||"Dave Hau" <nospam_dave_nospam_123@.nospam_netscape_nospam.net_ nospam> wrote
in message news:gCvub.33788$yj4.5497@.newssvr27.news.prodigy.c om...
> "Mathew Relick" <ticars@.yahoo.com> wrote in message
> news:dd84d8a7.0311181017.39d1c69@.posting.google.co m...
> > I'm trying to optimize some queries on an existing system, and I'm
> > noticing some odd behavior. I'm performing a join between several
> > tables, the final table being joined by the optimizer has a clustered
> > index on the field that it is using to join to the rest of the query,
> > but for some reason SQL Server doesn't seem to actually use this index
> > (it's doing an index scan instead of an index seek). Is there some
> > reason why SQL Server would not use a valid Clustered Index? I've
> > dropped and readded the index, but that doesn't seem to help. I don't
> This can happen if your table statistics are out of date. If SQL Server
> determines that the join will require more than x % of the table to be
> retrieved, then doing a clustered index scan may be faster than doing a
> clustered index seek (faster because an index scan can read the rows in a
> page sequentially, whereas an index seek has to traverse the B-tree
> structure. Sequential read is faster because you cut down on seek time,
and
> also because you may be able to read more than one page in a single I/O
> operation, since the data is sequential.) Try updating statistics and see
> if it helps:
> UPDATE STATISTICS <table_name> WITH FULLSCAN
> GO
> > know if it would be relevant, but the tables I'm working on are fairly
> > fat (2 to 7K bytes/row).
> > This is happening for several tables. I've been able to get around it
> > for some of the tables by creating a non-clustered index on all the
> > fields that are being queried so that the leaf pages don't need to be
> > loaded, but this isn't a valid solution for all of the tables I'm
> > struggling with.
> This is because when you create a new index, new statistics are generated
> for that index, so you have the most up-to-date statistics with your new
> index. Same if you rebuild your existing indexes.

I'm going to chime in because I think Dave has some good points here.
However, as I understand it, the original poster did rebuild the clustered
index, so there may be more to this problem than meets the eye.

One other thing that can happen is that the optimizer decides it's faster to
do a scan instead of a seek. This is particularly true if the result it
expects to return is a large percentage of the index. (i.e. if you have 100
rows and will return 80).

I'll be honest, I'm not sure exactly how this applies with a clustered
index.

> HTH,
> Dave
> > Any ideas? (and no, they aren't willing to redesign any of the
> > tables).

Query Operating System

Is it possible to query the operating system for disk space, memory size,
and other values. Are these values kept in a system database?
Thanks,
nivek
Hi Nivek,
Yes - you can definitely use Transact-SQL to query these values. For
example, to query what drive letters are on your machine, and determine how
much space is available you can run this:
EXEC master..xp_fixeddrives
To figure out memory available and other performance counters, you can query
the master.dbo.sysperfinfo system table.
Best Regards,
Joseph Sack
www.JoeSack.com
Author of "SQL Server 2000 Fast Answers for DBAs and Developers"
"nivek" wrote:

> Is it possible to query the operating system for disk space, memory size,
> and other values. Are these values kept in a system database?
> Thanks,
> nivek
>
>
|||You can also use xp_regread to query windows registry for a broader range of
values.
"Joseph Sack" <JosephSack@.discussions.microsoft.com> wrote in message
news:57A80ACA-ECE5-4D3E-879C-125B1057D773@.microsoft.com...[vbcol=seagreen]
> Hi Nivek,
> Yes - you can definitely use Transact-SQL to query these values. For
> example, to query what drive letters are on your machine, and determine
> how
> much space is available you can run this:
> EXEC master..xp_fixeddrives
> To figure out memory available and other performance counters, you can
> query
> the master.dbo.sysperfinfo system table.
> Best Regards,
> Joseph Sack
> www.JoeSack.com
> Author of "SQL Server 2000 Fast Answers for DBAs and Developers"
> "nivek" wrote:
|||Am using this SP to get disk space of drives on the server... It might be
helpful to you also...
I have created a job which calls this SP in the following manner,
exec sp_diskalert 'my email addr', 100000
Where 100000 is the limit in MB's. You can set it to any size. This limit
gives me the list of drives which are below 100 gig.
SP script is pasted below.
HTH
GYK
**************************************
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure sp_diskalert
@.RCPT VARCHAR(500),
@.LIMIT INT
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #T1(
DRVLETTER CHAR(1),
DRVSPACE INT
)
INSERT INTO #T1 EXEC master.dbo.xp_fixeddrives
/* GENERATE THE MESSAGE */
IF (SELECT COUNT(*) FROM #T1) > 0 AND LEN(@.RCPT) > 0 --CHECK THERE IS SOME
DATA AND A RECIPIENT
BEGIN
DECLARE @.MSG VARCHAR(400),
@.DLETTER VARCHAR(5),
@.DSPACE INT
SET @.DLETTER = (SELECT TOP 1 DRVLETTER FROM #T1 --GET FIRST DRIVE LETTER
WHERE DRVSPACE < @.LIMIT
ORDER BY DRVLETTER ASC)
SET @.DSPACE = (SELECT DRVSPACE FROM #T1 --GET THE DISK SPACE FOR THE LETTER
WHERE DRVLETTER = @.DLETTER)
SET @.MSG = @.DLETTER + ' is at ' + CONVERT(VARCHAR,@.DSPACE) --PUT THE VARS
INTO A MSG
+ 'MB' + CHAR(13) + CHAR(10)
WHILE (SELECT COUNT(*) FROM #T1 WHERE DRVSPACE < @.LIMIT AND DRVLETTER >
@.DLETTER) > 0
BEGIN--LOOP THROUGH DRIVE LETTERS AND REPEAT ABOVE
SET @.DLETTER = (SELECT TOP 1 DRVLETTER FROM #T1
WHERE DRVSPACE < @.LIMIT
AND DRVLETTER > @.DLETTER
ORDER BY DRVLETTER ASC)
SET @.DSPACE = (SELECT DRVSPACE FROM #T1
WHERE DRVLETTER = @.DLETTER)
SET @.MSG = @.MSG + @.DLETTER + ' is at ' + CONVERT(VARCHAR,@.DSPACE) + 'MB'
+ CHAR(13) + CHAR(10)
END
/* SEND THE MESSAGE */
IF CHARINDEX('@.',@.RCPT) > 0 --THERE IS AN @. SYMBOL IN THE RECIPIENT - SEND
EMAIL
BEGIN
DECLARE @.EMAIL VARCHAR(600)
SET @.EMAIL = 'EXEC master.dbo.xp_sendmail
@.recipients = ''' + @.RCPT + ''',
@.message = ''' + @.MSG + ''',
@.subject = ''!! LOW FREE DISK SPACE ON ' + @.@.SERVERNAME + ' !!'''
EXEC (@.EMAIL)
END
ELSE IF CHARINDEX('@.',@.RCPT) = 0 --THERE IS NO @. SYMBOL IN THE RECIPIENT -
NET SEND
BEGIN
--DETERMINE IF XP_CMDSHELL EXISTS
DECLARE @.FLAG BIT
SET @.FLAG = 1
IF NOT EXISTS(SELECT name FROM master..sysobjects WHERE name =
'XP_CMDSHELL')
SET @.FLAG = 0
--IF NOT RECREATE IT
IF @.FLAG = 0
BEGIN
EXEC sp_addextendedproc 'xp_cmdshell', 'xpsql70.dll'
PRINT 'ADDING XP_CMDSHELL'
END
--NET SEND MSG
DECLARE @.NETSEND VARCHAR(600)
SET @.MSG = 'ALERT - LOW FREE DISK SPACE ON ' + @.@.SERVERNAME + ' : ' + @.MSG
SET @.NETSEND = 'xp_cmdshell ''net send "' + RTRIM(@.RCPT) + '" '
+ LEFT(RTRIM(REPLACE(@.MSG,CHAR(13) + CHAR(10),', ')),LEN(@.MSG)-2) + ''''
EXEC (@.NETSEND)
--DROP XP_CMDSHELL IF IT DIDN'T EXIST
IF @.FLAG = 0
BEGIN
EXEC sp_dropextendedproc 'xp_cmdshell'
PRINT 'DROPPING XP_CMDSHELL'
END
END
END
/* CLEANUP */
DROP TABLE #T1
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
********************************************
"nivek" wrote:

> Is it possible to query the operating system for disk space, memory size,
> and other values. Are these values kept in a system database?
> Thanks,
> nivek
>
>

Query Operating System

Is it possible to query the operating system for disk space, memory size,
and other values. Are these values kept in a system database?
Thanks,
nivekHi Nivek,
Yes - you can definitely use Transact-SQL to query these values. For
example, to query what drive letters are on your machine, and determine how
much space is available you can run this:
EXEC master..xp_fixeddrives
To figure out memory available and other performance counters, you can query
the master.dbo.sysperfinfo system table.
Best Regards,
Joseph Sack
www.JoeSack.com
Author of "SQL Server 2000 Fast Answers for DBAs and Developers"
"nivek" wrote:
> Is it possible to query the operating system for disk space, memory size,
> and other values. Are these values kept in a system database?
> Thanks,
> nivek
>
>|||You can also use xp_regread to query windows registry for a broader range of
values.
"Joseph Sack" <JosephSack@.discussions.microsoft.com> wrote in message
news:57A80ACA-ECE5-4D3E-879C-125B1057D773@.microsoft.com...
> Hi Nivek,
> Yes - you can definitely use Transact-SQL to query these values. For
> example, to query what drive letters are on your machine, and determine
> how
> much space is available you can run this:
> EXEC master..xp_fixeddrives
> To figure out memory available and other performance counters, you can
> query
> the master.dbo.sysperfinfo system table.
> Best Regards,
> Joseph Sack
> www.JoeSack.com
> Author of "SQL Server 2000 Fast Answers for DBAs and Developers"
> "nivek" wrote:
>> Is it possible to query the operating system for disk space, memory size,
>> and other values. Are these values kept in a system database?
>> Thanks,
>> nivek
>>|||Am using this SP to get disk space of drives on the server... It might be
helpful to you also...
I have created a job which calls this SP in the following manner,
exec sp_diskalert 'my email addr', 100000
Where 100000 is the limit in MB's. You can set it to any size. This limit
gives me the list of drives which are below 100 gig.
SP script is pasted below.
HTH
GYK
**************************************
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure sp_diskalert
@.RCPT VARCHAR(500),
@.LIMIT INT
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #T1(
DRVLETTER CHAR(1),
DRVSPACE INT
)
INSERT INTO #T1 EXEC master.dbo.xp_fixeddrives
/* GENERATE THE MESSAGE */
IF (SELECT COUNT(*) FROM #T1) > 0 AND LEN(@.RCPT) > 0 --CHECK THERE IS SOME
DATA AND A RECIPIENT
BEGIN
DECLARE @.MSG VARCHAR(400),
@.DLETTER VARCHAR(5),
@.DSPACE INT
SET @.DLETTER = (SELECT TOP 1 DRVLETTER FROM #T1 --GET FIRST DRIVE LETTER
WHERE DRVSPACE < @.LIMIT
ORDER BY DRVLETTER ASC)
SET @.DSPACE = (SELECT DRVSPACE FROM #T1 --GET THE DISK SPACE FOR THE LETTER
WHERE DRVLETTER = @.DLETTER)
SET @.MSG = @.DLETTER + ' is at ' + CONVERT(VARCHAR,@.DSPACE) --PUT THE VARS
INTO A MSG
+ 'MB' + CHAR(13) + CHAR(10)
WHILE (SELECT COUNT(*) FROM #T1 WHERE DRVSPACE < @.LIMIT AND DRVLETTER >
@.DLETTER) > 0
BEGIN --LOOP THROUGH DRIVE LETTERS AND REPEAT ABOVE
SET @.DLETTER = (SELECT TOP 1 DRVLETTER FROM #T1
WHERE DRVSPACE < @.LIMIT
AND DRVLETTER > @.DLETTER
ORDER BY DRVLETTER ASC)
SET @.DSPACE = (SELECT DRVSPACE FROM #T1
WHERE DRVLETTER = @.DLETTER)
SET @.MSG = @.MSG + @.DLETTER + ' is at ' + CONVERT(VARCHAR,@.DSPACE) + 'MB'
+ CHAR(13) + CHAR(10)
END
/* SEND THE MESSAGE */
IF CHARINDEX('@.',@.RCPT) > 0 --THERE IS AN @. SYMBOL IN THE RECIPIENT - SEND
EMAIL
BEGIN
DECLARE @.EMAIL VARCHAR(600)
SET @.EMAIL = 'EXEC master.dbo.xp_sendmail
@.recipients = ''' + @.RCPT + ''',
@.message = ''' + @.MSG + ''',
@.subject = ''!! LOW FREE DISK SPACE ON ' + @.@.SERVERNAME + ' !!'''
EXEC (@.EMAIL)
END
ELSE IF CHARINDEX('@.',@.RCPT) = 0 --THERE IS NO @. SYMBOL IN THE RECIPIENT -
NET SEND
BEGIN
--DETERMINE IF XP_CMDSHELL EXISTS
DECLARE @.FLAG BIT
SET @.FLAG = 1
IF NOT EXISTS(SELECT name FROM master..sysobjects WHERE name ='XP_CMDSHELL')
SET @.FLAG = 0
--IF NOT RECREATE IT
IF @.FLAG = 0
BEGIN
EXEC sp_addextendedproc 'xp_cmdshell', 'xpsql70.dll'
PRINT 'ADDING XP_CMDSHELL'
END
--NET SEND MSG
DECLARE @.NETSEND VARCHAR(600)
SET @.MSG = 'ALERT - LOW FREE DISK SPACE ON ' + @.@.SERVERNAME + ' : ' + @.MSG
SET @.NETSEND = 'xp_cmdshell ''net send "' + RTRIM(@.RCPT) + '" '
+ LEFT(RTRIM(REPLACE(@.MSG,CHAR(13) + CHAR(10),', ')),LEN(@.MSG)-2) + ''''
EXEC (@.NETSEND)
--DROP XP_CMDSHELL IF IT DIDN'T EXIST
IF @.FLAG = 0
BEGIN
EXEC sp_dropextendedproc 'xp_cmdshell'
PRINT 'DROPPING XP_CMDSHELL'
END
END
END
/* CLEANUP */
DROP TABLE #T1
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
********************************************
"nivek" wrote:
> Is it possible to query the operating system for disk space, memory size,
> and other values. Are these values kept in a system database?
> Thanks,
> nivek
>
>

Query Notification - checking permissions

Hi all,

I am looking at replacing a polled system with Query Notification. However when I create the SqlDependency I need to be sure I have the correct permissions. I check the SqlClientPermissions via the Demand() method, but also want to ensure I have the correct DB permission given my current connection string. As I understand it I need to have the following permissions:

CREATE PROCEDURE, QUEUE, and SERVICE permissions
SUBSCRIBE QUERY NOTIFICATIONS
SELECT on underlying tables
RECEIVE on QueryNotificationErrorsQueue

I check most of these via the 'has_perms_by_name' function, but cannot find the correct syntax to check for RECEIVE on QueryNotificationErrorsQueue. I would also love to find a way to do this via SMO instead of issuing SQl commands. Also am I missing any checks ....

Finally, I have also run into the problem whereby SQL issues the following error:

The activated proc [dbo].[SqlQueryNotificationStoredProcedure-1fd90369-7781-4bad-a1b7-e1b56e328374] running on queue ImlHostDB.dbo.SqlQueryNotificationService-1fd90369-7781-4bad-a1b7-e1b56e328374 output the following: 'Could not obtain information about Windows NT group/user 'EMEA\DyerN', error code 0x54b.'

I ran into this issue when I bought my machine out of sleep mode with it no longer connected to the network. Is their no way to get a error notification. In this situation I will just stop seeing notification and without looing at the ErrorLog believe their is nothing wrong.

Many Thanks, Nick

The SQL Server instance in question cannot communicate with the Active Directory to validate the Windows accounts (like EMEA\DyerN) and hits error 0x54b, which is ERROR_NO_SUCH_DOMAIN. You should contact your network/security administrator to diagnose and troubleshoot the problem, as is unrelated to Service Broker or Query Notifications (are you working on a laptop disconnected from domain by any chance?) Alternatively, you could use SQL users/logins instead of Windows users/logins.|||

This was true, I was disconnected from the domain. My hope however was for the query notification to fire signally that an error had occurred and change notifcations could not be delivered. This would allow me to handle it programatically and re-register with different credentials or fall back to polling for data changes.

Currently as it stands if I get disconnected from the domain my query notifications will simply stop and I will receive no notification that an error has occurred. This does not leave me with a robust solution.

Thanks, Nick

BTW An idea on determine if I have the correct permissions to RECEIVE on QueryNotificationErrorsQueue ?

|||

NickUk wrote:

BTW An idea on determine if I have the correct permissions to RECEIVE on QueryNotificationErrorsQueue ?

select * from sys.fn_my_permissions('dbo.QueryNotificationErrorsQueue','object')

|||

Query Notifications run 'execute as owner' so if a database is owned by a domain account, query notifications fail in the event a domain control cannot be contacted

Basically, change owner to SA to avoid the issue.

See this writeup

http://aspadvice.com/blogs/ssmith/archive/2006/11/06/SqlDependency-Issue-Resolved.aspx

Query Notification - checking permissions

Hi all,

I am looking at replacing a polled system with Query Notification. However when I create the SqlDependency I need to be sure I have the correct permissions. I check the SqlClientPermissions via the Demand() method, but also want to ensure I have the correct DB permission given my current connection string. As I understand it I need to have the following permissions:

CREATE PROCEDURE, QUEUE, and SERVICE permissions
SUBSCRIBE QUERY NOTIFICATIONS
SELECT on underlying tables
RECEIVE on QueryNotificationErrorsQueue

I check most of these via the 'has_perms_by_name' function, but cannot find the correct syntax to check for RECEIVE on QueryNotificationErrorsQueue. I would also love to find a way to do this via SMO instead of issuing SQl commands. Also am I missing any checks ....

Finally, I have also run into the problem whereby SQL issues the following error:

The activated proc [dbo].[SqlQueryNotificationStoredProcedure-1fd90369-7781-4bad-a1b7-e1b56e328374] running on queue ImlHostDB.dbo.SqlQueryNotificationService-1fd90369-7781-4bad-a1b7-e1b56e328374 output the following: 'Could not obtain information about Windows NT group/user 'EMEA\DyerN', error code 0x54b.'

I ran into this issue when I bought my machine out of sleep mode with it no longer connected to the network. Is their no way to get a error notification. In this situation I will just stop seeing notification and without looing at the ErrorLog believe their is nothing wrong.

Many Thanks, Nick

The SQL Server instance in question cannot communicate with the Active Directory to validate the Windows accounts (like EMEA\DyerN) and hits error 0x54b, which is ERROR_NO_SUCH_DOMAIN. You should contact your network/security administrator to diagnose and troubleshoot the problem, as is unrelated to Service Broker or Query Notifications (are you working on a laptop disconnected from domain by any chance?) Alternatively, you could use SQL users/logins instead of Windows users/logins.|||

This was true, I was disconnected from the domain. My hope however was for the query notification to fire signally that an error had occurred and change notifcations could not be delivered. This would allow me to handle it programatically and re-register with different credentials or fall back to polling for data changes.

Currently as it stands if I get disconnected from the domain my query notifications will simply stop and I will receive no notification that an error has occurred. This does not leave me with a robust solution.

Thanks, Nick

BTW An idea on determine if I have the correct permissions to RECEIVE on QueryNotificationErrorsQueue ?

|||

NickUk wrote:

BTW An idea on determine if I have the correct permissions to RECEIVE on QueryNotificationErrorsQueue ?

select * from sys.fn_my_permissions('dbo.QueryNotificationErrorsQueue','object')

|||

Query Notifications run 'execute as owner' so if a database is owned by a domain account, query notifications fail in the event a domain control cannot be contacted

Basically, change owner to SA to avoid the issue.

See this writeup

http://aspadvice.com/blogs/ssmith/archive/2006/11/06/SqlDependency-Issue-Resolved.aspx

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)

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)

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)

Query logic not working...

I have a little system of 3 tables Job, employees and times. This times table has the fields times_id, employee_id and job_id

I'm trying to have a query that pull of employees that don't have a certain job_id yet. I'm going to put this data in a table so the user knows they are available for that job. The code i have isn't working, and i'm not sure why.

SELECT
DISTINCT times.employee_id, employee.employee_name
FROM employee
INNER JOIN times ON employee.employee_id = times.employee_id
WHERE (times.job_id <> @.job_id)

Thanks in advance for any help. I'm sure I missing someting silly, or maybe i need to have a stored procedure involved?... Thanks!

Try a subquery:

SELECT
DISTINCT employee_id, employee_name
FROM employee
WHERE employee_id not in
(SELECT employee_id FROM times
WHERE (job_id= @.job_id) )

|||That worked great, I've totally forgot about sub-queries. Thanks a lot Iori Jay.|||

OR

SELECT
DISTINCT employee_id, employee_name
FROM employee
WHERE not exists (SELECT employee_id FROM times
WHERE (job_id= @.job_id) )