Query Plan Question

I'm running SQL Server 2000 Std SP3 on Windows 2000 Standard SP4.
I have the following query:
declare @.FromDate as DATETIME
SET @.FromDate = '2004-03-10'
Select Count(*)
From URLS
Where (URLString like '%homepage%')
And AddedOn >= @.FromDate
Select Count(*)
From URLS
Where (URLString like '%homepage%')
And AddedOn >= '2004-03-10'
The first query which uses @.FromDate does a table scan. The second query th
at has the date hard coded uses the index on the AddedOn date field.
My question is why doesn't the first query also use the index? There are 10
Million + rows in the table.
Thanks,
StephenIf you use a variable in a WHERE clause, then the optimizer doesn't know
what value you are looking for (the optimizer optimizes statement by
statement). So, it will have to guess number of rows to be returned. I don't
recall the values it guesses (you find them in the Inside SQL Server book),
but I think that it is either 10% or 25% for greater then. Say you have 10
million rows, this means that SQL Server will read 1 million rows. Say you
have an NC index, then SQL Server will potentially need to jump to a data
page for each row. This means 1 million data page accesses.
Above is just to give you an understand about how the optimizer works. Note
that using a variable and a stored procedure parameter are two different
things!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Stephen Schissler" <anonymous@.discussions.microsoft.com> wrote in message
news:4A78A446-AC10-4F41-9EED-A048B3B5864D@.microsoft.com...
> I'm running SQL Server 2000 Std SP3 on Windows 2000 Standard SP4.
> I have the following query:
> declare @.FromDate as DATETIME
> SET @.FromDate = '2004-03-10'
> Select Count(*)
> From URLS
> Where (URLString like '%homepage%')
> And AddedOn >= @.FromDate
>
> Select Count(*)
> From URLS
> Where (URLString like '%homepage%')
> And AddedOn >= '2004-03-10'
> The first query which uses @.FromDate does a table scan. The second query
that has the date hard coded uses the index on the AddedOn date field.
> My question is why doesn't the first query also use the index? There are
10 Million + rows in the table.
> Thanks,
> Stephen|||It guesses?
So if I have 1 record of 10 million where the AddedOn date is equal to '2004
-03-10' and I pass this value as a variable it will do a table scan? That s
eems to me to be the wrong thing to do. I've updated the statistics on the
AddedOn date field using th
e FULLSCAN option and it still does a table scan which I find very disturbin
g. I'm now wondering how many other queries that use variables as a paramet
er are choosing the wrong query plan due to this.
Thanks,
Stephen|||Stephen Schissler wrote:

> It guesses?
> So if I have 1 record of 10 million where the AddedOn date is equal to '2004-03-10
' and I pass this value as a variable it will do a table scan? That seems to me to
be the wrong thing to do. I've updated the statistics on the AddedOn date field usi
ng
the FULLSCAN option and it still does a table scan which I find very disturbing. I'm now w
ondering how many other queries that use variables as a parameter are choosing the wrong qu
ery plan due to this.
> Thanks,
> Stephen
>
If you run it as a stored procedure and use the where clause as a
parameter you will notice different results.
Aaron Weiker
http://blogs.sqladvice.com/aweiker
http://aaronweiker.com/|||> It guesses?
What else can it do? Well, not a wild guess, it has its rules. The optimizer
does not know the value of the variable, as it optimizes statement by
statement. (Yes, one could question why that it, but it is the way SQL
Server work.) As I said, for different predicates, SQL Server estimates to
return different percentage of rows. Details in Inside SQL Server.
Statistics has nothing to do with this.
If you use a constant or a stored procedure parameter, it s a different
thing, though!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Stephen Schissler" <anonymous@.discussions.microsoft.com> wrote in message
news:23380C0C-0820-4155-BA43-D574741DC19C@.microsoft.com...
> It guesses?
> So if I have 1 record of 10 million where the AddedOn date is equal to
'2004-03-10' and I pass this value as a variable it will do a table scan?
That seems to me to be the wrong thing to do. I've updated the statistics
on the AddedOn date field using the FULLSCAN option and it still does a
table scan which I find very disturbing. I'm now wondering how many other
queries that use variables as a parameter are choosing the wrong query plan
due to this.
> Thanks,
> Stephen

Query Performance

Hi all,

I′m using SQL Server 2000 in the Windows 2003 Server with 2GB RAM
and 2 dual core processors.
There is an application that access the SQL Server.
Several queries are with problems of performance.
I cannot change these queries ′cause the application
was made by other IT company.

[code]
SELECT * FROM SE1010 SE1
WHERE E1_FILIAL = ' '
AND D_E_L_E_T_ <> '*'
AND E1_CLIENTE BETWEEN ' ' AND 'ZZZZZZ'
AND E1_PREFIXO BETWEEN ' ' AND 'ZZZ'
AND E1_NUM BETWEEN ' ' AND 'ZZZZZZ'
AND E1_PORTADO BETWEEN ' ' AND 'ZZZ'
AND E1_VENCREA BETWEEN '20010101' AND '20070228'
AND (E1_MULTNAT = '1' OR (E1_NATUREZ BETWEEN ' ' AND 'ZZZZZZZZZZ'))
AND E1_EMISSAO BETWEEN '20010101' AND '20070228'
AND E1_LOJA BETWEEN ' ' AND 'ZZ'
AND E1_EMISSAO <= '20070301'
AND ((E1_EMIS1 BETWEEN '20060101' AND '20070228')
OR E1_EMISSAO BETWEEN '20060101' AND '20070228')
ORDER BY E1_FILIAL,E1_PREFIXO,E1_NUM,E1_PARCELA,E1_TIPO

The execution plan is:

|--Sort(ORDER BY:([SE1].[E1_PREFIXO] ASC, [SE1].[E1_NUM] ASC,
[SE1].[E1_PARCELA] ASC, [SE1].[E1_TIPO] ASC))
|--Filter(WHERE:((((([SE1].[E1_EMISSAO]>='20010101'
AND [SE1].[E1_EMISSAO]<='20070228') AND [SE1].[E1_LOJA]>=' ')
AND [SE1].[E1_LOJA]<='ZZ') AND [SE1].[E1_EMISSAO]<='20070301')
AND (([SE1].[E1_EMIS1]>='20060101' AND
[SE1].[E1_EMIS1]<='20070228') OR
|--Clustered Index Scan(OBJECT:([DADOSADV].[dbo].[SE1010].[SE1010_PK]
AS [SE1]), WHERE:(((((((((((([SE1].[E1_FILIAL]=' '
AND [SE1].[D_E_L_E_T_]<>'*') AND [SE1].[E1_CLIENTE]>=' ')
AND [SE1].[E1_CLIENTE]<='ZZZZZZ') AND [SE1].[E1_PREFIXO]>=''

[/code]

I′ve created index for the columns that are used by where clause
and I′ve performed the DBCC INDEXDEFRAG and DBREINDEX, but after I′ve performed
theses functions, the performance fell.

What can I do to improve query performance?

thanks!!!!Moving to engine forum.

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
>

Query never ends execution

Hello

I have a .Net application that calls an stored procedure. When it does, the execution goes and never ends (I have to kill the windows process). When I call the sp from within the Management Studio, it also never ends executing and I have to cancel the query. But, when I call it immediately after, it takes 45 seconds to complete.

Now, the sp has several parts and I have made that it prints a message at the end of each part so that I can read where it stops. Strange enough, it completes all parts except the last one, which has the form INSERT INTO myLocalTable SELECT * FROM MyRemoteTable. But if I execute the Select independetly, I discover that it brings no rows! Now, many of the @.@.rowcount printed after the execution of the other parts shows zero rows involved or just a few. I am not using cursors, each part is an UPDATE statement or an INSERT.

TestMachine1 runs SQL2005 SP2 and has as linked server myRemoteServer (SQL2000) server. The stored procedure in TestMachine1 inserts rows to a table in myRemoteServer and brings back some rows.

What could be wrong?What could be wrong?
I'll guess it is one of these issues:
A) Gerbils nibbling on your network cable.
B) Global warming affecting your server environment.
C) Bears. Big nasty ones.
D) Some problem with the code you did not bother posting.|||I vote for bears. they are always on the threatdown causing trouble.|||Here is the general structure of the problematic stored procedure (Consider that there are 2 remote tables and 2 local tables instead of just one, and that the operations are made for both of them in a similar fashion):

declare @.LastUpdate datetime
declare @.Workstation varchar(250)

set @.Workstation=host_name()

SET NOCOUNT ON

execute spGetLastUpdate @.LastUpdate output

insert into Synonym_MyRemoteTable1Temp
select
@.Workstation,
ID,
Value1,
Value2,
Value3
from myLocalTable1
where UpdateTimeStamp>@.LastUpdate

execute Synonym_spMyRemoteProcedure @.Workstation -- Explained below
/*
This remote procedure makes an update and an insert as follows:

update MyRemoteTable1
set
Value1=MyRemoteTable1Temp.Value1,
Value2=MyRemoteTable1Temp.Value2,
Value3=MyRemoteTable1Temp.Value3
from MyRemoteTable1
inner join MyRemoteTable1Temp on MyRemoteTable1Temp.ID=MyRemoteTable1.ID and WorkStation=@.WorkStation

insert into MyRemoteTable1
(
ID,
Value1,
Value2,
Value3,
UpdateTimeStamp
)
select
ID,
Value1,
Value2,
Value3,
GetDate()
from MyRemoteTable1Temp
where
WorkStation=@.WorkStation and ID not in (select ID from MyRemoteTable1)
*/

update MyLocalTable1
set
Value1=T.Value1,
Value2=T.Value2,
Value3=T.Value3,
UpdateTimeStamp=GetDate()
from MyLocalTable1
inner join
(
select
ID,
Value1,
Value2,
Value3
from Synonym_MyRemoteTable1
where UpdateTimeStamp>@.LastUpdate and
WorkStation<>@.Workstation

) as T on T.ID=MyLocalTable1.ID

insert into MyLocalTable1
(
ID,
Value1,
Value2,
Value3,
UpdateTimeStamp
)
select
ID,
Value1,
Value2,
Value3,
GetDate()
from Synonym_MyRemoteTable1
where
UpdateTimeStamp>@.LastUpdate and
Workstation<>@.WorkStation and
ID not in (select ID from MyLocalTable1)