Thursday, March 29, 2012
Boolean in sqlserver 2000 and sqlserver 2005
In sqlserver (2000 & 2005) for a Boolean column what datatype is best
supported in terms of performance and best practice.
For a BOOLEAN is it a
best practice to use CHAR(1) which stores (Y / N )
or
best practice to use INT which stores (0 / 1 )
Thanks In adv
ThirumaranThis depends somewhat on what you are going to do with it and how your
application will handle it. But I typically like to use a TINYINT. It only
takes up 1 byte but still allows you to expand from a simple Bool to a
status type value without changing the datatype. It is also cleaner in most
cases to deal with than a Bit datatype.
--
Andrew J. Kelly SQL MVP
"thirumaran" <thirumaran@.discussions.microsoft.com> wrote in message
news:A22D9167-8FE4-4018-85FF-84D0A0D31FBD@.microsoft.com...
> Hi ,
> In sqlserver (2000 & 2005) for a Boolean column what datatype is best
> supported in terms of performance and best practice.
> For a BOOLEAN is it a
> best practice to use CHAR(1) which stores (Y / N )
> or
> best practice to use INT which stores (0 / 1 )
> Thanks In adv
> Thirumaran
>
>
>|||It depends on your business requirements
It could be CHAR(1) which stores (Y / N ) or TINYINT 0/1 or BIT 0/1
It is worth to mention that you should not allow NULL's in that CASE or as
I said it depends on what you are trying to achive
"thirumaran" <thirumaran@.discussions.microsoft.com> wrote in message
news:A22D9167-8FE4-4018-85FF-84D0A0D31FBD@.microsoft.com...
> Hi ,
> In sqlserver (2000 & 2005) for a Boolean column what datatype is best
> supported in terms of performance and best practice.
> For a BOOLEAN is it a
> best practice to use CHAR(1) which stores (Y / N )
> or
> best practice to use INT which stores (0 / 1 )
> Thanks In adv
> Thirumaran
>
>
>
Boolean in sqlserver 2000 and sqlserver 2005
In sqlserver (2000 & 2005) for a Boolean column what datatype is best
supported in terms of performance and best practice.
For a BOOLEAN is it a
best practice to use CHAR(1) which stores (Y / N )
or
best practice to use INT which stores (0 / 1 )
Thanks In adv
ThirumaranThis depends somewhat on what you are going to do with it and how your
application will handle it. But I typically like to use a TINYINT. It only
takes up 1 byte but still allows you to expand from a simple Bool to a
status type value without changing the datatype. It is also cleaner in most
cases to deal with than a Bit datatype.
Andrew J. Kelly SQL MVP
"thirumaran" <thirumaran@.discussions.microsoft.com> wrote in message
news:A22D9167-8FE4-4018-85FF-84D0A0D31FBD@.microsoft.com...
> Hi ,
> In sqlserver (2000 & 2005) for a Boolean column what datatype is best
> supported in terms of performance and best practice.
> For a BOOLEAN is it a
> best practice to use CHAR(1) which stores (Y / N )
> or
> best practice to use INT which stores (0 / 1 )
> Thanks In adv
> Thirumaran
>
>
>|||It depends on your business requirements
It could be CHAR(1) which stores (Y / N ) or TINYINT 0/1 or BIT 0/1
It is worth to mention that you should not allow NULL's in that CASE or as
I said it depends on what you are trying to achive
"thirumaran" <thirumaran@.discussions.microsoft.com> wrote in message
news:A22D9167-8FE4-4018-85FF-84D0A0D31FBD@.microsoft.com...
> Hi ,
> In sqlserver (2000 & 2005) for a Boolean column what datatype is best
> supported in terms of performance and best practice.
> For a BOOLEAN is it a
> best practice to use CHAR(1) which stores (Y / N )
> or
> best practice to use INT which stores (0 / 1 )
> Thanks In adv
> Thirumaran
>
>
>
Thursday, March 22, 2012
Book recommendation on performance
published in 1999. It states that "SELECT ... INTO" statements end up
locking the entire database of the target table. Since the tempdb is
also involved (in many cases), this creates major deadlocks for the
entire database and all users. It suggests using the "INSERT ...
SELECT" form instead.
Considering that the book is somehow dated, is this recommendation
still valid, especially on target sizes of up to 5 million records?php newbie (newtophp2000@.yahoo.com) writes:
> I have the Transact-SQL Programming book from O'Reilly. It was
> published in 1999. It states that "SELECT ... INTO" statements end up
> locking the entire database of the target table. Since the tempdb is
> also involved (in many cases), this creates major deadlocks for the
> entire database and all users. It suggests using the "INSERT ...
> SELECT" form instead.
> Considering that the book is somehow dated, is this recommendation
> still valid, especially on target sizes of up to 5 million records?
To a large extent, no. The author seems to have had SQL 6.5 in mind, where
SELECT INTO a temptable, indeed to bring a server to a stand still. The
problem is that SELECT INTO creates the temp table, and then goes on to
fill it with data. Since the query is one transaction, it keeps a lock on
the system tables until the query has completed. This was fatal in SQL 6.5
which only had page locks. In SQL7 and SQL2000 where you have row locks,
the impact on other processes is much smaller.
The advantage of SELECT INTO is that is that is minimally logged, so
SELECT INTO #tmp for five million rows can be faster and take less
toll on the server than CREATE TABLE INSERT INTO.
Nevertheless, there are contexts where SELECT INTO is an inferior choice
over CREATE TABLE not talking about a table variable. Say that you already
have a transaction in progress, and you creating many small tables
repeatedly within this transaction. SELECT INTO takes out more locks on
than CREATE TABLE, so you acquire a whole lot more locks with SELECT
INTO, and this can have an impact on performance. Case in point: I had
a procedure which suddently started to perform much slower than before.
This procedure performs some complex data updating in an iterative
fashion. My profiling pointed to a seeminginly innocent query which
appeared to take longer and longer time as the procedure proceeded.
Eventually I found the answer in a trigger (which was not affected by
this query). I had replaced direct deferences to "inserted" with temp
table created through "SELECT * INTO #inserted FROM inserted".
To summarize: SELECT INTO is fine for single-time queries on large
tables. It is bad to have in triggers in tables which maninly are
updated one row at a time.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Tuesday, March 20, 2012
book on performance tuning
a good book for this? I don't need a book to tell me where to put Indexes. I
need a book that will tell me "if you see this in the Graphical Eecution
Plan, re-write your code like this". Is there such a thing?
Also, is it possible to be a top notch query tuner without being a top notch
Developer? Im not a top notch Developer, but the query tuning is defiantley
on my shoulders. Ive always gotten by... but want to take these skills to
the next level.
TIA, ChrisR"ChrisR" <noemail@.bla.com> wrote in message
news:#Qidm24ZFHA.3596@.tk2msftngp13.phx.gbl...
> I need to sharpen my performance tuning skills... fast. Does anyone know
of
> a good book for this? I don't need a book to tell me where to put Indexes.
I
> need a book that will tell me "if you see this in the Graphical Eecution
> Plan, re-write your code like this". Is there such a thing?
> Also, is it possible to be a top notch query tuner without being a top
notch
> Developer? Im not a top notch Developer, but the query tuning is
defiantley
> on my shoulders. Ive always gotten by... but want to take these skills to
> the next level.
>
> TIA, ChrisR
>
I don't know of any good books...
This site is excellent however.
http://www.sql-server-performance.com
Rick Sawtell
MCT, MCSD, MCDBA|||I would recomment SQL Server Query Performance Tuning Distilled (orginally
from Curlingstone publishers, now from Apress I think).
I found that an excellent book. I think the way you get to the next level is
through experience, you wont get that in any book. Books can only take you s
o
far, they are excellent to a point.
Also, for those kind of questions "if you see this in the Graphical Eecution
> Plan, re-write your code like this" you'll get the best advice from forums like th
is one.
"ChrisR" wrote:
> I need to sharpen my performance tuning skills... fast. Does anyone know o
f
> a good book for this? I don't need a book to tell me where to put Indexes.
I
> need a book that will tell me "if you see this in the Graphical Eecution
> Plan, re-write your code like this". Is there such a thing?
> Also, is it possible to be a top notch query tuner without being a top not
ch
> Developer? Im not a top notch Developer, but the query tuning is defiantle
y
> on my shoulders. Ive always gotten by... but want to take these skills to
> the next level.
>
> TIA, ChrisR
>
>|||Microsoft SQL Server 2000 Performance Optimization and Tuning Handbook
Ken England
http://search.barnesandnoble.com/bo...555582419&itm=5
"ChrisR" <noemail@.bla.com> wrote in message
news:%23Qidm24ZFHA.3596@.tk2msftngp13.phx.gbl...
> I need to sharpen my performance tuning skills... fast. Does anyone know
of
> a good book for this? I don't need a book to tell me where to put Indexes.
I
> need a book that will tell me "if you see this in the Graphical Eecution
> Plan, re-write your code like this". Is there such a thing?
> Also, is it possible to be a top notch query tuner without being a top
notch
> Developer? Im not a top notch Developer, but the query tuning is
defiantley
> on my shoulders. Ive always gotten by... but want to take these skills to
> the next level.
>
> TIA, ChrisR
>|||Great book... it has practical examples that illustrate the material
covered.
I recommend it also!
"NH" <NH@.discussions.microsoft.com> wrote in message
news:AF77B42A-4151-4611-B56A-05C3A4FA94EF@.microsoft.com...
> I would recomment SQL Server Query Performance Tuning Distilled (orginally
> from Curlingstone publishers, now from Apress I think).
> I found that an excellent book. I think the way you get to the next level
is
> through experience, you wont get that in any book. Books can only take you
so
> far, they are excellent to a point.
> Also, for those kind of questions "if you see this in the Graphical
Eecution
forums like this one.
> "ChrisR" wrote:
>
of
Indexes. I
notch
defiantley
to|||I think I'm gonna go with this one. I was just reading Amazon reviews on
this and several other books on this topic. A lot of reviews for this book
mention that screen prints are provided of the Exec Plan, Profiler, etc.
Therefore its not just theoretical, but practical. Too much theory puts me
to sleep.
Thanks guys.
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:u5dAkH7ZFHA.3400@.tk2msftngp13.phx.gbl...
> Great book... it has practical examples that illustrate the material
> covered.
> I recommend it also!
> "NH" <NH@.discussions.microsoft.com> wrote in message
> news:AF77B42A-4151-4611-B56A-05C3A4FA94EF@.microsoft.com...
(orginally
level
> is
you
> so
> Eecution
> forums like this one.
know
> of
> Indexes. I
Eecution
> notch
> defiantley
> to
>sql
Thursday, March 8, 2012
Blocking caused by Replication
I wonder how I can pursue a blocking problem caused by SQL Replication.
We are having a performance issue on one of our busy sql server 2000 (build
818) machines. There is tran.repl. Publisher db and subscriber dbs are on the
same box.
We have seen this problem after we moved sql server to a new box. SW and HW
configuration are the same.
There are two head blockers.
Waittype is PAGELATCH_SH
Inputbuffer is sp_MSget_repl_commands
CPU is %100 and LogReader is always running for hours until we stop it.
Any idea?
Thanks,
Ada
SQL Server DBA
Does this apply?
http://support.microsoft.com/kb/812263/en-us
Also what happens when you stop the log reader agent and restart it? Is the
condition cleared?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ada" <Ada@.discussions.microsoft.com> wrote in message
news:F913F6BA-56EB-4CE6-A972-089334B84479@.microsoft.com...
> Hi All,
> I wonder how I can pursue a blocking problem caused by SQL Replication.
> We are having a performance issue on one of our busy sql server 2000
> (build
> 818) machines. There is tran.repl. Publisher db and subscriber dbs are on
> the
> same box.
> We have seen this problem after we moved sql server to a new box. SW and
> HW
> configuration are the same.
> There are two head blockers.
> Waittype is PAGELATCH_SH
> Inputbuffer is sp_MSget_repl_commands
> CPU is %100 and LogReader is always running for hours until we stop it.
> Any idea?
> Thanks,
> Ada
> --
> SQL Server DBA
|||No, it does not apply.
The log reader was unresponsive/hung. Resetting up repl cleared the problem.
I wonder if it might be a sql bug?
Thanks for your response.
SQL Server DBA
"Hilary Cotter" wrote:
> Does this apply?
> http://support.microsoft.com/kb/812263/en-us
> Also what happens when you stop the log reader agent and restart it? Is the
> condition cleared?
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Ada" <Ada@.discussions.microsoft.com> wrote in message
> news:F913F6BA-56EB-4CE6-A972-089334B84479@.microsoft.com...
>
>
|||On Tue, 19 Sep 2006 01:45:01 -0700, Ada
<Ada@.discussions.microsoft.com> wrote:
>We have seen this problem after we moved sql server to a new box. SW and HW
>configuration are the same.
No idea, but under the hit-it-with-a-stick school of repair, have you
tried removing replication and then restarting it?
J.
|||Removing replication and setting up again resolves the problem temporarily.
SQL Server DBA
"JXStern" wrote:
> On Tue, 19 Sep 2006 01:45:01 -0700, Ada
> <Ada@.discussions.microsoft.com> wrote:
> No idea, but under the hit-it-with-a-stick school of repair, have you
> tried removing replication and then restarting it?
> J.
>
|||On Wed, 20 Sep 2006 18:24:02 -0700, Ada
<Ada@.discussions.microsoft.com> wrote:
>Removing replication and setting up again resolves the problem temporarily.
Er, ... what does sp_who2 show as the head of the blocking chain?
Don't think I ever have tried it with publisher and subscriber on the
same machine. This is one-way, right? If it's two-way, and you
forget to turn on the bit, things would get ugly, might be something
like this.
J.
|||It's one way.
SQL Server DBA
"JXStern" wrote:
> On Wed, 20 Sep 2006 18:24:02 -0700, Ada
> <Ada@.discussions.microsoft.com> wrote:
>
> Er, ... what does sp_who2 show as the head of the blocking chain?
> Don't think I ever have tried it with publisher and subscriber on the
> same machine. This is one-way, right? If it's two-way, and you
> forget to turn on the bit, things would get ugly, might be something
> like this.
> J.
>
>
Blocking caused by Replication
I wonder how I can pursue a blocking problem caused by SQL Replication.
We are having a performance issue on one of our busy sql server 2000 (build
818) machines. There is tran.repl. Publisher db and subscriber dbs are on th
e
same box.
We have seen this problem after we moved sql server to a new box. SW and HW
configuration are the same.
There are two head blockers.
Waittype is PAGELATCH_SH
Inputbuffer is sp_MSget_repl_commands
CPU is %100 and LogReader is always running for hours until we stop it.
Any idea?
Thanks,
Ada
--
SQL Server DBADoes this apply?
http://support.microsoft.com/kb/812263/en-us
Also what happens when you stop the log reader agent and restart it? Is the
condition cleared?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ada" <Ada@.discussions.microsoft.com> wrote in message
news:F913F6BA-56EB-4CE6-A972-089334B84479@.microsoft.com...
> Hi All,
> I wonder how I can pursue a blocking problem caused by SQL Replication.
> We are having a performance issue on one of our busy sql server 2000
> (build
> 818) machines. There is tran.repl. Publisher db and subscriber dbs are on
> the
> same box.
> We have seen this problem after we moved sql server to a new box. SW and
> HW
> configuration are the same.
> There are two head blockers.
> Waittype is PAGELATCH_SH
> Inputbuffer is sp_MSget_repl_commands
> CPU is %100 and LogReader is always running for hours until we stop it.
> Any idea?
> Thanks,
> Ada
> --
> SQL Server DBA|||No, it does not apply.
The log reader was unresponsive/hung. Resetting up repl cleared the problem.
I wonder if it might be a sql bug'
Thanks for your response.
SQL Server DBA
"Hilary Cotter" wrote:
> Does this apply?
> http://support.microsoft.com/kb/812263/en-us
> Also what happens when you stop the log reader agent and restart it? Is th
e
> condition cleared?
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Ada" <Ada@.discussions.microsoft.com> wrote in message
> news:F913F6BA-56EB-4CE6-A972-089334B84479@.microsoft.com...
>
>|||On Tue, 19 Sep 2006 01:45:01 -0700, Ada
<Ada@.discussions.microsoft.com> wrote:
>We have seen this problem after we moved sql server to a new box. SW and HW
>configuration are the same.
No idea, but under the hit-it-with-a-stick school of repair, have you
tried removing replication and then restarting it?
J.|||Removing replication and setting up again resolves the problem temporarily.
SQL Server DBA
"JXStern" wrote:
> On Tue, 19 Sep 2006 01:45:01 -0700, Ada
> <Ada@.discussions.microsoft.com> wrote:
> No idea, but under the hit-it-with-a-stick school of repair, have you
> tried removing replication and then restarting it?
> J.
>|||On Wed, 20 Sep 2006 18:24:02 -0700, Ada
<Ada@.discussions.microsoft.com> wrote:
>Removing replication and setting up again resolves the problem temporarily.
Er, ... what does sp_who2 show as the head of the blocking chain?
Don't think I ever have tried it with publisher and subscriber on the
same machine. This is one-way, right? If it's two-way, and you
forget to turn on the bit, things would get ugly, might be something
like this.
J.|||It's one way.
SQL Server DBA
"JXStern" wrote:
> On Wed, 20 Sep 2006 18:24:02 -0700, Ada
> <Ada@.discussions.microsoft.com> wrote:
>
> Er, ... what does sp_who2 show as the head of the blocking chain?
> Don't think I ever have tried it with publisher and subscriber on the
> same machine. This is one-way, right? If it's two-way, and you
> forget to turn on the bit, things would get ugly, might be something
> like this.
> J.
>
>
Blocking caused by Replication
I wonder how I can pursue a blocking problem caused by SQL Replication.
We are having a performance issue on one of our busy sql server 2000 (build
818) machines. There is tran.repl. Publisher db and subscriber dbs are on the
same box.
We have seen this problem after we moved sql server to a new box. SW and HW
configuration are the same.
There are two head blockers.
Waittype is PAGELATCH_SH
Inputbuffer is sp_MSget_repl_commands
CPU is %100 and LogReader is always running for hours until we stop it.
Any idea?
Thanks,
Ada
--
SQL Server DBADoes this apply?
http://support.microsoft.com/kb/812263/en-us
Also what happens when you stop the log reader agent and restart it? Is the
condition cleared?
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ada" <Ada@.discussions.microsoft.com> wrote in message
news:F913F6BA-56EB-4CE6-A972-089334B84479@.microsoft.com...
> Hi All,
> I wonder how I can pursue a blocking problem caused by SQL Replication.
> We are having a performance issue on one of our busy sql server 2000
> (build
> 818) machines. There is tran.repl. Publisher db and subscriber dbs are on
> the
> same box.
> We have seen this problem after we moved sql server to a new box. SW and
> HW
> configuration are the same.
> There are two head blockers.
> Waittype is PAGELATCH_SH
> Inputbuffer is sp_MSget_repl_commands
> CPU is %100 and LogReader is always running for hours until we stop it.
> Any idea?
> Thanks,
> Ada
> --
> SQL Server DBA|||No, it does not apply.
The log reader was unresponsive/hung. Resetting up repl cleared the problem.
I wonder if it might be a sql bug'
Thanks for your response.
--
SQL Server DBA
"Hilary Cotter" wrote:
> Does this apply?
> http://support.microsoft.com/kb/812263/en-us
> Also what happens when you stop the log reader agent and restart it? Is the
> condition cleared?
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Ada" <Ada@.discussions.microsoft.com> wrote in message
> news:F913F6BA-56EB-4CE6-A972-089334B84479@.microsoft.com...
> > Hi All,
> >
> > I wonder how I can pursue a blocking problem caused by SQL Replication.
> >
> > We are having a performance issue on one of our busy sql server 2000
> > (build
> > 818) machines. There is tran.repl. Publisher db and subscriber dbs are on
> > the
> > same box.
> > We have seen this problem after we moved sql server to a new box. SW and
> > HW
> > configuration are the same.
> >
> > There are two head blockers.
> >
> > Waittype is PAGELATCH_SH
> > Inputbuffer is sp_MSget_repl_commands
> >
> > CPU is %100 and LogReader is always running for hours until we stop it.
> >
> > Any idea?
> >
> > Thanks,
> >
> > Ada
> > --
> > SQL Server DBA
>
>|||On Tue, 19 Sep 2006 01:45:01 -0700, Ada
<Ada@.discussions.microsoft.com> wrote:
>We have seen this problem after we moved sql server to a new box. SW and HW
>configuration are the same.
No idea, but under the hit-it-with-a-stick school of repair, have you
tried removing replication and then restarting it?
J.|||Removing replication and setting up again resolves the problem temporarily.
--
SQL Server DBA
"JXStern" wrote:
> On Tue, 19 Sep 2006 01:45:01 -0700, Ada
> <Ada@.discussions.microsoft.com> wrote:
> >We have seen this problem after we moved sql server to a new box. SW and HW
> >configuration are the same.
> No idea, but under the hit-it-with-a-stick school of repair, have you
> tried removing replication and then restarting it?
> J.
>|||On Wed, 20 Sep 2006 18:24:02 -0700, Ada
<Ada@.discussions.microsoft.com> wrote:
>Removing replication and setting up again resolves the problem temporarily.
Er, ... what does sp_who2 show as the head of the blocking chain?
Don't think I ever have tried it with publisher and subscriber on the
same machine. This is one-way, right? If it's two-way, and you
forget to turn on the bit, things would get ugly, might be something
like this.
J.|||It's one way.
--
SQL Server DBA
"JXStern" wrote:
> On Wed, 20 Sep 2006 18:24:02 -0700, Ada
> <Ada@.discussions.microsoft.com> wrote:
> >
> >Removing replication and setting up again resolves the problem temporarily.
> Er, ... what does sp_who2 show as the head of the blocking chain?
> Don't think I ever have tried it with publisher and subscriber on the
> same machine. This is one-way, right? If it's two-way, and you
> forget to turn on the bit, things would get ugly, might be something
> like this.
> J.
>
>
Blocking
would be nice to be notified when blocking is occurring.Originally posted by ToddBritt
Can a Performance Alert be set up to detect blocking on a server? We do not get deadlocks but we do get a bit of blocking going on and it
would be nice to be notified when blocking is occurring.
You can acheive this by using a combination of Scheduled Tasks, stored procedures and Alerts.
Write a stored procedure that generates a user-defined error message whenever there is blocking. Blocking can be checked by querying the sysprocesses system table.
Define a scheduled task that runs this stored procedure for every n minutes.
Define an Alert on the specific error message and configure it to send a mail.
Saturday, February 25, 2012
BLOB performance SQL 2005 Do's and Don'ts
I have a content based site with over a 1000 downloadable word docs
which are currently stored on a server filesystem.
however these are split in muliple folder paths for different clients
and i am trying to find a more efficient way of doing this. i have
been looking into storing the files as blobs in a db rather than
searching on a filename on a file IO etc
in SQL 2000 this was frowned upon as it was so poorly managed but has
this been improved in 2005 or would i be better off using the same
methods i have in place now?
any comments?If you're searching on filenames, why don't you store the filename in the
database?
"km200281" <k.a.mortimer@.gmail.com> wrote in message
news:1147267971.088679.264550@.u72g2000cwu.googlegroups.com...
> Hi,
> I have a content based site with over a 1000 downloadable word docs
> which are currently stored on a server filesystem.
> however these are split in muliple folder paths for different clients
> and i am trying to find a more efficient way of doing this. i have
> been looking into storing the files as blobs in a db rather than
> searching on a filename on a file IO etc
> in SQL 2000 this was frowned upon as it was so poorly managed but has
> this been improved in 2005 or would i be better off using the same
> methods i have in place now?
> any comments?
>|||Hi,
that is what we do now however a large number of updates are done to
the site on a regular basis and this is because we deal with multiple
versions of the same document in different locations its not easy to
manage hence my query regarding the use of BLOBs
Friday, February 24, 2012
blob and network packet size
what is the best network packet size setting? The resource kit indicates that
it is 8080. Is that correct, or is it a typo that should say 8192?
2) Does the client connection have to match the network packet size setting
for it to be used?
In general the network packet size setting should be left alone, it is not
going to give you much one way or another in terms of performance.
The main reason I say this is everything has to go across TCP-IP, which has
a limit of ~1430 bytes per packet. So the TDS packet size is not the
limiting factor. Everything is squeezed down into ~1430 byte TCP packet in
the end.
Another problem with cranking it up is that it causes SQL Server to also
chew up more memory holding larger packets, reducing scalability. So 4K
default is good. Network packet size > 8000 bytes should be avoided for
server scalability reasons, this causes SQL to allocate packets out of
mem2leave area instead of normal buffer pool.
Matt Neerincx [MSFT]
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.
"dp" <dp@.discussions.microsoft.com> wrote in message
news:1DAAA61D-5512-45A1-83F4-BAD4EF1CDDB7@.microsoft.com...
> 1) For performance of BLOB only data that will have at least 100 KB per
> BLOB,
> what is the best network packet size setting? The resource kit indicates
> that
> it is 8080. Is that correct, or is it a typo that should say 8192?
> 2) Does the client connection have to match the network packet size
> setting
> for it to be used?
>
>
|||Your reply make me quite confused. The following are from Chapter 11 - Using
BLOBs in SQL Server 2000 Resource Kit. url:
http://www.microsoft.com/technet/pro...rt3/c1161.mspx
If your database application sends or receives large amounts of BLOB data,
consider changing the packet size of the chunks of data that transfer
requests and results between clients and servers. To change or set the packet
size, use the network packet size option in Configuration Options. A packet
size larger than the 4,096-byte default may improve efficiency by lowering
the network reads and writes. A packet size of 8,080 bytes is best for
performance in SQL Server 2000 and SQL Server 7.0.
So which configuration should I follow? From your reply, tha statement above
is not quite correct, isn't it?
Anyway, would you (or someone) mind give me some direction on how to
configure a sql server 2000 which only hold blob data for best performance?
thanks much,
dp
m
"Matt Neerincx [MS]" wrote:
> In general the network packet size setting should be left alone, it is not
> going to give you much one way or another in terms of performance.
> The main reason I say this is everything has to go across TCP-IP, which has
> a limit of ~1430 bytes per packet. So the TDS packet size is not the
> limiting factor. Everything is squeezed down into ~1430 byte TCP packet in
> the end.
> Another problem with cranking it up is that it causes SQL Server to also
> chew up more memory holding larger packets, reducing scalability. So 4K
> default is good. Network packet size > 8000 bytes should be avoided for
> server scalability reasons, this causes SQL to allocate packets out of
> mem2leave area instead of normal buffer pool.
> Matt Neerincx [MSFT]
> This posting is provided "AS IS", with no warranties, and confers no rights.
> Please do not send email directly to this alias. This alias is for newsgroup
> purposes only.
> "dp" <dp@.discussions.microsoft.com> wrote in message
> news:1DAAA61D-5512-45A1-83F4-BAD4EF1CDDB7@.microsoft.com...
>
>
blob and network packet size
,
what is the best network packet size setting? The resource kit indicates tha
t
it is 8080. Is that correct, or is it a typo that should say 8192?
2) Does the client connection have to match the network packet size setting
for it to be used?In general the network packet size setting should be left alone, it is not
going to give you much one way or another in terms of performance.
The main reason I say this is everything has to go across TCP-IP, which has
a limit of ~1430 bytes per packet. So the TDS packet size is not the
limiting factor. Everything is squeezed down into ~1430 byte TCP packet in
the end.
Another problem with cranking it up is that it causes SQL Server to also
chew up more memory holding larger packets, reducing scalability. So 4K
default is good. Network packet size > 8000 bytes should be avoided for
server scalability reasons, this causes SQL to allocate packets out of
mem2leave area instead of normal buffer pool.
Matt Neerincx [MSFT]
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.
"dp" <dp@.discussions.microsoft.com> wrote in message
news:1DAAA61D-5512-45A1-83F4-BAD4EF1CDDB7@.microsoft.com...
> 1) For performance of BLOB only data that will have at least 100 KB per
> BLOB,
> what is the best network packet size setting? The resource kit indicates
> that
> it is 8080. Is that correct, or is it a typo that should say 8192?
> 2) Does the client connection have to match the network packet size
> setting
> for it to be used?
>
>|||Your reply make me quite confused. The following are from Chapter 11 - Using
BLOBs in SQL Server 2000 Resource Kit. url:
[url]http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part3/c1161.mspx[/url
]
If your database application sends or receives large amounts of BLOB data,
consider changing the packet size of the chunks of data that transfer
requests and results between clients and servers. To change or set the packe
t
size, use the network packet size option in Configuration Options. A packet
size larger than the 4,096-byte default may improve efficiency by lowering
the network reads and writes. A packet size of 8,080 bytes is best for
performance in SQL Server 2000 and SQL Server 7.0.
So which configuration should I follow? From your reply, tha statement above
is not quite correct, isn't it?
Anyway, would you (or someone) mind give me some direction on how to
configure a sql server 2000 which only hold blob data for best performance?
thanks much,
dp
--
m
"Matt Neerincx [MS]" wrote:
> In general the network packet size setting should be left alone, it is not
> going to give you much one way or another in terms of performance.
> The main reason I say this is everything has to go across TCP-IP, which ha
s
> a limit of ~1430 bytes per packet. So the TDS packet size is not the
> limiting factor. Everything is squeezed down into ~1430 byte TCP packet in
> the end.
> Another problem with cranking it up is that it causes SQL Server to also
> chew up more memory holding larger packets, reducing scalability. So 4K
> default is good. Network packet size > 8000 bytes should be avoided for
> server scalability reasons, this causes SQL to allocate packets out of
> mem2leave area instead of normal buffer pool.
> Matt Neerincx [MSFT]
> This posting is provided "AS IS", with no warranties, and confers no right
s.
> Please do not send email directly to this alias. This alias is for newsgro
up
> purposes only.
> "dp" <dp@.discussions.microsoft.com> wrote in message
> news:1DAAA61D-5512-45A1-83F4-BAD4EF1CDDB7@.microsoft.com...
>
>
Sunday, February 12, 2012
bizarre performance issue
CPU and RAM available. There is no blocking. I cannot find any table scans,
(other than - - 120 row temp table scans) nor does the Index Tuning Wizrd
recommend that I add anything. No software other than SQL Server and some
CommVault backup software are on the box. But still, performance is all over
the place. Sometimes everything is humming right along between 3 and 5
seconds, but later on it will be between 40 and 50 seconds. Of course I see
all of these Procs in Profiler, but everything appears to be OK with them.
Ive been on this all day and am really at a loss as to what to do from here.
Any ideas?
--
TIA,
ChrisRThat is not much to go on. What is it you are doing? 3 to 5 seconds isn't
humming along in my book but it is hard to say without seeing what you are
trying to do. No that it should make that much of a difference but maybe
you are getting a lot of recompiles due to the temp table populations. Have
you tried replacing them with table vars? They sound like the optimal size
for table vars over temp tables.
--
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:594022ED-21B3-44F6-84A3-803F877D243D@.microsoft.com...
> Ive got a box that is having bizarre performance behavior. There is plenty
> of
> CPU and RAM available. There is no blocking. I cannot find any table
> scans,
> (other than - - 120 row temp table scans) nor does the Index Tuning Wizrd
> recommend that I add anything. No software other than SQL Server and some
> CommVault backup software are on the box. But still, performance is all
> over
> the place. Sometimes everything is humming right along between 3 and 5
> seconds, but later on it will be between 40 and 50 seconds. Of course I
> see
> all of these Procs in Profiler, but everything appears to be OK with them.
> Ive been on this all day and am really at a loss as to what to do from
> here.
> Any ideas?
> --
> TIA,
> ChrisR|||ChrisR wrote:
> Ive got a box that is having bizarre performance behavior. There is
> plenty of CPU and RAM available. There is no blocking. I cannot find
> any table scans, (other than - - 120 row temp table scans) nor does
> the Index Tuning Wizrd recommend that I add anything. No software
> other than SQL Server and some CommVault backup software are on the
> box. But still, performance is all over the place. Sometimes
> everything is humming right along between 3 and 5 seconds, but later
> on it will be between 40 and 50 seconds. Of course I see all of these
> Procs in Profiler, but everything appears to be OK with them. Ive
> been on this all day and am really at a loss as to what to do from
> here. Any ideas?
When you say the procs, as they appear in Profiler, appear ok, what do
you mean? If they are in fact appearing within your expected performance
parameters, you should see CPU, Reads, and Duration figures that are
normal. Duration should be in the 3,000 - 5,000ms range as you mentioned
and in all likelyhood, performance at the client should be good as well.
If OTOH you are seeing a Duration of 30,000ms or elevated CPU or Read
activity, then I'm guessing that Profiler is showing you there is a
problem.
Can you be more specific about any differences in the Profiler output
between good and bad performance.
David Gugick
Quest Software
www.imceda.com
www.quest.com
bizarre performance issue
f
CPU and RAM available. There is no blocking. I cannot find any table scans,
(other than - - 120 row temp table scans) nor does the Index Tuning Wizrd
recommend that I add anything. No software other than SQL Server and some
CommVault backup software are on the box. But still, performance is all over
the place. Sometimes everything is humming right along between 3 and 5
seconds, but later on it will be between 40 and 50 seconds. Of course I see
all of these Procs in Profiler, but everything appears to be OK with them.
Ive been on this all day and am really at a loss as to what to do from here.
Any ideas?
TIA,
ChrisRThat is not much to go on. What is it you are doing? 3 to 5 seconds isn't
humming along in my book but it is hard to say without seeing what you are
trying to do. No that it should make that much of a difference but maybe
you are getting a lot of recompiles due to the temp table populations. Have
you tried replacing them with table vars? They sound like the optimal size
for table vars over temp tables.
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:594022ED-21B3-44F6-84A3-803F877D243D@.microsoft.com...
> Ive got a box that is having bizarre performance behavior. There is plenty
> of
> CPU and RAM available. There is no blocking. I cannot find any table
> scans,
> (other than - - 120 row temp table scans) nor does the Index Tuning Wizrd
> recommend that I add anything. No software other than SQL Server and some
> CommVault backup software are on the box. But still, performance is all
> over
> the place. Sometimes everything is humming right along between 3 and 5
> seconds, but later on it will be between 40 and 50 seconds. Of course I
> see
> all of these Procs in Profiler, but everything appears to be OK with them.
> Ive been on this all day and am really at a loss as to what to do from
> here.
> Any ideas?
> --
> TIA,
> ChrisR|||ChrisR wrote:
> Ive got a box that is having bizarre performance behavior. There is
> plenty of CPU and RAM available. There is no blocking. I cannot find
> any table scans, (other than - - 120 row temp table scans) nor does
> the Index Tuning Wizrd recommend that I add anything. No software
> other than SQL Server and some CommVault backup software are on the
> box. But still, performance is all over the place. Sometimes
> everything is humming right along between 3 and 5 seconds, but later
> on it will be between 40 and 50 seconds. Of course I see all of these
> Procs in Profiler, but everything appears to be OK with them. Ive
> been on this all day and am really at a loss as to what to do from
> here. Any ideas?
When you say the procs, as they appear in Profiler, appear ok, what do
you mean? If they are in fact appearing within your expected performance
parameters, you should see CPU, Reads, and Duration figures that are
normal. Duration should be in the 3,000 - 5,000ms range as you mentioned
and in all likelyhood, performance at the client should be good as well.
If OTOH you are seeing a Duration of 30,000ms or elevated CPU or Read
activity, then I'm guessing that Profiler is showing you there is a
problem.
Can you be more specific about any differences in the Profiler output
between good and bad performance.
David Gugick
Quest Software
www.imceda.com
www.quest.com
bizarre performance issue
CPU and RAM available. There is no blocking. I cannot find any table scans,
(other than - - 120 row temp table scans) nor does the Index Tuning Wizrd
recommend that I add anything. No software other than SQL Server and some
CommVault backup software are on the box. But still, performance is all over
the place. Sometimes everything is humming right along between 3 and 5
seconds, but later on it will be between 40 and 50 seconds. Of course I see
all of these Procs in Profiler, but everything appears to be OK with them.
Ive been on this all day and am really at a loss as to what to do from here.
Any ideas?
TIA,
ChrisR
That is not much to go on. What is it you are doing? 3 to 5 seconds isn't
humming along in my book but it is hard to say without seeing what you are
trying to do. No that it should make that much of a difference but maybe
you are getting a lot of recompiles due to the temp table populations. Have
you tried replacing them with table vars? They sound like the optimal size
for table vars over temp tables.
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:594022ED-21B3-44F6-84A3-803F877D243D@.microsoft.com...
> Ive got a box that is having bizarre performance behavior. There is plenty
> of
> CPU and RAM available. There is no blocking. I cannot find any table
> scans,
> (other than - - 120 row temp table scans) nor does the Index Tuning Wizrd
> recommend that I add anything. No software other than SQL Server and some
> CommVault backup software are on the box. But still, performance is all
> over
> the place. Sometimes everything is humming right along between 3 and 5
> seconds, but later on it will be between 40 and 50 seconds. Of course I
> see
> all of these Procs in Profiler, but everything appears to be OK with them.
> Ive been on this all day and am really at a loss as to what to do from
> here.
> Any ideas?
> --
> TIA,
> ChrisR
|||ChrisR wrote:
> Ive got a box that is having bizarre performance behavior. There is
> plenty of CPU and RAM available. There is no blocking. I cannot find
> any table scans, (other than - - 120 row temp table scans) nor does
> the Index Tuning Wizrd recommend that I add anything. No software
> other than SQL Server and some CommVault backup software are on the
> box. But still, performance is all over the place. Sometimes
> everything is humming right along between 3 and 5 seconds, but later
> on it will be between 40 and 50 seconds. Of course I see all of these
> Procs in Profiler, but everything appears to be OK with them. Ive
> been on this all day and am really at a loss as to what to do from
> here. Any ideas?
When you say the procs, as they appear in Profiler, appear ok, what do
you mean? If they are in fact appearing within your expected performance
parameters, you should see CPU, Reads, and Duration figures that are
normal. Duration should be in the 3,000 - 5,000ms range as you mentioned
and in all likelyhood, performance at the client should be good as well.
If OTOH you are seeing a Duration of 30,000ms or elevated CPU or Read
activity, then I'm guessing that Profiler is showing you there is a
problem.
Can you be more specific about any differences in the Profiler output
between good and bad performance.
David Gugick
Quest Software
www.imceda.com
www.quest.com
Bizarre performance hit
development machine.
The remote machine is a Intel P4, 3.20GHz + 1G. The development machine is an AMD X2 3800+ dual
processor with 2G memory. Subjectively, I feel the development machine is noticeably faster in most
ways.
On the remote machine, I execute a SP which inserts approximately 6000 records. From the profiler, I
see each insertion takes about 50 Reads and about 0 Duration, which is acceptable.
After synchronizing the database logins with the those in the Master table on the development
machine, I execute the same SP on the development machine. The Reads are about 7500 and the Duration
around 3200!
Can anyone suggest reasons why this differential might occur? I really do need help. I'm not a DBA
but a developer. But it doesn't take a DBA to know that this won't fly.
TIA
Run sp_updatestats after a restore and then try it.
Andrew J. Kelly SQL MVP
"larzeb" <larzeb@.community.nospam> wrote in message
news:5os5k15iub07uqubpdqb8acgg7vcaq0grj@.4ax.com...
>I create backups of a remote, production database and copy it after
>compression to a local,
> development machine.
> The remote machine is a Intel P4, 3.20GHz + 1G. The development machine is
> an AMD X2 3800+ dual
> processor with 2G memory. Subjectively, I feel the development machine is
> noticeably faster in most
> ways.
> On the remote machine, I execute a SP which inserts approximately 6000
> records. From the profiler, I
> see each insertion takes about 50 Reads and about 0 Duration, which is
> acceptable.
> After synchronizing the database logins with the those in the Master table
> on the development
> machine, I execute the same SP on the development machine. The Reads are
> about 7500 and the Duration
> around 3200!
> Can anyone suggest reasons why this differential might occur? I really do
> need help. I'm not a DBA
> but a developer. But it doesn't take a DBA to know that this won't fly.
> TIA
|||Thanks for the reply, Andrew. I'm sorry to say it had no effect.
On Tue, 4 Oct 2005 18:28:55 -0400, "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote:
>Run sp_updatestats after a restore and then try it.
|||Are you sure there were no indexes added to the remote machine between the
time the backup was created and now? If the stats are the same (try running
in on the remote machine and see if it changes) then they should be the same
plan. Can you post the code for the sp?
Andrew J. Kelly SQL MVP
"larzeb" <larzeb@.community.nospam> wrote in message
news:bg46k11u0j542q5ua7dddol0g2fqfi58jt@.4ax.com... [vbcol=seagreen]
> Thanks for the reply, Andrew. I'm sorry to say it had no effect.
> On Tue, 4 Oct 2005 18:28:55 -0400, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote:
|||May be optimizer is not using the right indexes, you can try forcing the index.
Mohammed.
"Andrew J. Kelly" wrote:
> Are you sure there were no indexes added to the remote machine between the
> time the backup was created and now? If the stats are the same (try running
> in on the remote machine and see if it changes) then they should be the same
> plan. Can you post the code for the sp?
> --
> Andrew J. Kelly SQL MVP
>
> "larzeb" <larzeb@.community.nospam> wrote in message
> news:bg46k11u0j542q5ua7dddol0g2fqfi58jt@.4ax.com...
>
>
|||You did run sp_updatestats in the correct database context not the default
for [master]?
i.e.
USE [MyDB]
GO
exec sp_updatestats
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"larzeb" <larzeb@.community.nospam> wrote in message
news:bg46k11u0j542q5ua7dddol0g2fqfi58jt@.4ax.com... [vbcol=seagreen]
> Thanks for the reply, Andrew. I'm sorry to say it had no effect.
> On Tue, 4 Oct 2005 18:28:55 -0400, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote:
|||Andrew,
Sorry for the delay - DSL down for over 24 hours.
No changes made to anything execpt restoring DB, syncying user ids from Master, and running the
sp_updatestats. I also went to the remote, production machine and copied the BAK file to DVD, rather
than use the compressed BAK. The results were the same.
The SP and associated table definitions follow. I abridged some of the tables for simplicity.
exec usp_Mailings_Ins @.MailCampaignDetailID = 68, @.AddressID = 1639896, @.PersonID = 1659603,
@.BatchID = 411, @.TrayNo = 1, @.SerialNo = 3, @.NextMailDate = 'Dec 31 2100 12:00AM', @.OEL =
N'****************AUTO**5-DIGIT 90210', @.MailID = @.P1 output
select @.P1
CREATE PROCEDURE [dbo].usp_Mailings_Ins
@.MailCampaignDetailID int,
@.AddressID int,
@.PersonID int,
@.BatchID int,
@.TrayNo int,
@.SerialNo int,
@.NextMailDate DATETIME,
@.OEL varchar(50),
@.MailID int OUTPUT
AS
INSERT INTO [dbo].[Mailings] (
[MailCampaignDetailID],
[AddressID],
[PersonID],
[BatchID],
[TrayNo],
[SerialNo],
NextMailDate,
OEL
) VALUES (
@.MailCampaignDetailID,
@.AddressID,
@.PersonID,
@.BatchID,
@.TrayNo,
@.SerialNo,
@.NextMailDate,
@.OEL
)
SET @.MailID = SCOPE_IDENTITY()
GO
CREATE TABLE [dbo].[MailingBatch] (
[BatchID] [int] IDENTITY (1, 1) NOT NULL ,
[CompID] [int] NOT NULL ,
[MailCampaignDetailID] [int] NOT NULL ,
[BatchType] [int] NOT NULL ,
[PostageType] [int] NULL ,
[Cost] [decimal](18, 0) NOT NULL ,
[parameters] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[dateAdded] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[MailCampaignDetail] (
[MailCampaignDetailID] [int] IDENTITY (1, 1) NOT NULL ,
[MailCampaignID] [int] NOT NULL ,
...
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[AddressValid] (
[AddressID] [int] NOT NULL ,
...
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Person] (
[PersonID] [int] IDENTITY (1, 1) NOT NULL ,
[AddressID] [int] NOT NULL ,
...
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Mailings] (
[MailID] [int] IDENTITY (1, 1) NOT NULL ,
[MailCampaignDetailID] [int] NOT NULL ,
[AddressID] [int] NOT NULL ,
[PersonID] [int] NOT NULL ,
[BatchID] [int] NULL ,
[TrayNo] [int] NULL ,
[SerialNo] [int] NULL ,
[NextMailDate] [datetime] NULL ,
[MailDate] [smalldatetime] NOT NULL ,
[OEL] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MailingBatch] WITH NOCHECK ADD
CONSTRAINT [PK_Batch] PRIMARY KEY CLUSTERED
(
[BatchID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MailCampaignDetail] WITH NOCHECK ADD
CONSTRAINT [PK_MailCampaignDetail] PRIMARY KEY CLUSTERED
(
[MailCampaignDetailID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AddressValid] WITH NOCHECK ADD
CONSTRAINT [PK_AddressValid] PRIMARY KEY CLUSTERED
(
[AddressID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Person] WITH NOCHECK ADD
CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
(
[PersonID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Mailings] WITH NOCHECK ADD
CONSTRAINT [PK_Mailings] PRIMARY KEY CLUSTERED
(
[MailID]
) ON [PRIMARY]
GO
CREATE INDEX [IX_Mailings_BatchID] ON [dbo].[Mailings]([BatchID], [SerialNo]) ON [PRIMARY]
GO
CREATE INDEX [IX_Mailings_PersonID] ON [dbo].[Mailings]([PersonID]) ON [PRIMARY]
GO
CREATE INDEX [IX_Mailings_MCDID_AddressID] ON [dbo].[Mailings]([MailCampaignDetailID],
[AddressID]) ON [PRIMARY]
GO
CREATE INDEX [IX_Mailings_AddressID] ON [dbo].[Mailings]([AddressID]) ON [PRIMARY]
GO
CREATE INDEX [Mailings27] ON [dbo].[Mailings]([AddressID], [MailCampaignDetailID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Mailings] ADD
CONSTRAINT [FK_Mailings_AddressValid] FOREIGN KEY
(
[AddressID]
) REFERENCES [dbo].[AddressValid] (
[AddressID]
),
CONSTRAINT [FK_Mailings_MCD] FOREIGN KEY
(
[MailCampaignDetailID]
) REFERENCES [dbo].[MailCampaignDetail] (
[MailCampaignDetailID]
),
CONSTRAINT [FK_Mailings_Person] FOREIGN KEY
(
[PersonID]
) REFERENCES [dbo].[Person] (
[PersonID]
)
GO
On Tue, 4 Oct 2005 19:54:56 -0400, "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote:
>Are you sure there were no indexes added to the remote machine between the
>time the backup was created and now? If the stats are the same (try running
>in on the remote machine and see if it changes) then they should be the same
>plan. Can you post the code for the sp?
|||I was sure I ran sp_updatestats against the appropriate DB.
On Wed, 05 Oct 2005 06:13:44 GMT, "Nik Marshall-Blank" <Nik@.here.com> wrote:
>You did run sp_updatestats in the correct database context not the default
>for [master]?
>i.e.
>USE [MyDB]
>GO
>exec sp_updatestats
|||Mohammed,
I'm afraid I don't know how to "force" an index. Can you give me an example?
On Tue, 4 Oct 2005 18:08:03 -0700, "Mohammed" <Mohammed@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>May be optimizer is not using the right indexes, you can try forcing the index.
>Mohammed.
>"Andrew J. Kelly" wrote:
|||Well one thing I see is that you should put SET NOCOUNT ON at the beginning
of your sp but that should be the same on both.
Andrew J. Kelly SQL MVP
"larzeb" <larzeb@.community.nospam> wrote in message
news:fs4bk1de83u04keblbk0qnnr0hit4fvtae@.4ax.com... [vbcol=seagreen]
> Andrew,
> Sorry for the delay - DSL down for over 24 hours.
> No changes made to anything execpt restoring DB, syncying user ids from
> Master, and running the
> sp_updatestats. I also went to the remote, production machine and copied
> the BAK file to DVD, rather
> than use the compressed BAK. The results were the same.
> The SP and associated table definitions follow. I abridged some of the
> tables for simplicity.
> exec usp_Mailings_Ins @.MailCampaignDetailID = 68, @.AddressID = 1639896,
> @.PersonID = 1659603,
> @.BatchID = 411, @.TrayNo = 1, @.SerialNo = 3, @.NextMailDate = 'Dec 31 2100
> 12:00AM', @.OEL =
> N'****************AUTO**5-DIGIT 90210', @.MailID = @.P1 output
> select @.P1
> CREATE PROCEDURE [dbo].usp_Mailings_Ins
> @.MailCampaignDetailID int,
> @.AddressID int,
> @.PersonID int,
> @.BatchID int,
> @.TrayNo int,
> @.SerialNo int,
> @.NextMailDate DATETIME,
> @.OEL varchar(50),
> @.MailID int OUTPUT
> AS
> INSERT INTO [dbo].[Mailings] (
> [MailCampaignDetailID],
> [AddressID],
> [PersonID],
> [BatchID],
> [TrayNo],
> [SerialNo],
> NextMailDate,
> OEL
> ) VALUES (
> @.MailCampaignDetailID,
> @.AddressID,
> @.PersonID,
> @.BatchID,
> @.TrayNo,
> @.SerialNo,
> @.NextMailDate,
> @.OEL
> )
> SET @.MailID = SCOPE_IDENTITY()
> GO
> CREATE TABLE [dbo].[MailingBatch] (
> [BatchID] [int] IDENTITY (1, 1) NOT NULL ,
> [CompID] [int] NOT NULL ,
> [MailCampaignDetailID] [int] NOT NULL ,
> [BatchType] [int] NOT NULL ,
> [PostageType] [int] NULL ,
> [Cost] [decimal](18, 0) NOT NULL ,
> [parameters] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [dateAdded] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[MailCampaignDetail] (
> [MailCampaignDetailID] [int] IDENTITY (1, 1) NOT NULL ,
> [MailCampaignID] [int] NOT NULL ,
> ...
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[AddressValid] (
> [AddressID] [int] NOT NULL ,
> ...
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Person] (
> [PersonID] [int] IDENTITY (1, 1) NOT NULL ,
> [AddressID] [int] NOT NULL ,
> ...
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Mailings] (
> [MailID] [int] IDENTITY (1, 1) NOT NULL ,
> [MailCampaignDetailID] [int] NOT NULL ,
> [AddressID] [int] NOT NULL ,
> [PersonID] [int] NOT NULL ,
> [BatchID] [int] NULL ,
> [TrayNo] [int] NULL ,
> [SerialNo] [int] NULL ,
> [NextMailDate] [datetime] NULL ,
> [MailDate] [smalldatetime] NOT NULL ,
> [OEL] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[MailingBatch] WITH NOCHECK ADD
> CONSTRAINT [PK_Batch] PRIMARY KEY CLUSTERED
> (
> [BatchID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[MailCampaignDetail] WITH NOCHECK ADD
> CONSTRAINT [PK_MailCampaignDetail] PRIMARY KEY CLUSTERED
> (
> [MailCampaignDetailID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[AddressValid] WITH NOCHECK ADD
> CONSTRAINT [PK_AddressValid] PRIMARY KEY CLUSTERED
> (
> [AddressID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Person] WITH NOCHECK ADD
> CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
> (
> [PersonID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Mailings] WITH NOCHECK ADD
> CONSTRAINT [PK_Mailings] PRIMARY KEY CLUSTERED
> (
> [MailID]
> ) ON [PRIMARY]
> GO
> CREATE INDEX [IX_Mailings_BatchID] ON [dbo].[Mailings]([BatchID],
> [SerialNo]) ON [PRIMARY]
> GO
> CREATE INDEX [IX_Mailings_PersonID] ON [dbo].[Mailings]([PersonID]) ON
> [PRIMARY]
> GO
> CREATE INDEX [IX_Mailings_MCDID_AddressID] ON
> [dbo].[Mailings]([MailCampaignDetailID],
> [AddressID]) ON [PRIMARY]
> GO
> CREATE INDEX [IX_Mailings_AddressID] ON [dbo].[Mailings]([AddressID]) ON
> [PRIMARY]
> GO
> CREATE INDEX [Mailings27] ON [dbo].[Mailings]([AddressID],
> [MailCampaignDetailID]) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Mailings] ADD
> CONSTRAINT [FK_Mailings_AddressValid] FOREIGN KEY
> (
> [AddressID]
> ) REFERENCES [dbo].[AddressValid] (
> [AddressID]
> ),
> CONSTRAINT [FK_Mailings_MCD] FOREIGN KEY
> (
> [MailCampaignDetailID]
> ) REFERENCES [dbo].[MailCampaignDetail] (
> [MailCampaignDetailID]
> ),
> CONSTRAINT [FK_Mailings_Person] FOREIGN KEY
> (
> [PersonID]
> ) REFERENCES [dbo].[Person] (
> [PersonID]
> )
> GO
>
> On Tue, 4 Oct 2005 19:54:56 -0400, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote:
Bizarre performance hit
ion to a local,
development machine.
The remote machine is a Intel P4, 3.20GHz + 1G. The development machine is a
n AMD X2 3800+ dual
processor with 2G memory. Subjectively, I feel the development machine is no
ticeably faster in most
ways.
On the remote machine, I execute a SP which inserts approximately 6000 recor
ds. From the profiler, I
see each insertion takes about 50 Reads and about 0 Duration, which is accep
table.
After synchronizing the database logins with the those in the Master table o
n the development
machine, I execute the same SP on the development machine. The Reads are abo
ut 7500 and the Duration
around 3200!
Can anyone suggest reasons why this differential might occur? I really do ne
ed help. I'm not a DBA
but a developer. But it doesn't take a DBA to know that this won't fly.
TIARun sp_updatestats after a restore and then try it.
Andrew J. Kelly SQL MVP
"larzeb" <larzeb@.community.nospam> wrote in message
news:5os5k15iub07uqubpdqb8acgg7vcaq0grj@.
4ax.com...
>I create backups of a remote, production database and copy it after
>compression to a local,
> development machine.
> The remote machine is a Intel P4, 3.20GHz + 1G. The development machine is
> an AMD X2 3800+ dual
> processor with 2G memory. Subjectively, I feel the development machine is
> noticeably faster in most
> ways.
> On the remote machine, I execute a SP which inserts approximately 6000
> records. From the profiler, I
> see each insertion takes about 50 Reads and about 0 Duration, which is
> acceptable.
> After synchronizing the database logins with the those in the Master table
> on the development
> machine, I execute the same SP on the development machine. The Reads are
> about 7500 and the Duration
> around 3200!
> Can anyone suggest reasons why this differential might occur? I really do
> need help. I'm not a DBA
> but a developer. But it doesn't take a DBA to know that this won't fly.
> TIA|||Thanks for the reply, Andrew. I'm sorry to say it had no effect.
On Tue, 4 Oct 2005 18:28:55 -0400, "Andrew J. Kelly" <sqlmvpnooospam@.shadhaw
k.com> wrote:
>Run sp_updatestats after a restore and then try it.|||Are you sure there were no indexes added to the remote machine between the
time the backup was created and now? If the stats are the same (try running
in on the remote machine and see if it changes) then they should be the same
plan. Can you post the code for the sp?
Andrew J. Kelly SQL MVP
"larzeb" <larzeb@.community.nospam> wrote in message
news:bg46k11u0j542q5ua7dddol0g2fqfi58jt@.
4ax.com...[vbcol=seagreen]
> Thanks for the reply, Andrew. I'm sorry to say it had no effect.
> On Tue, 4 Oct 2005 18:28:55 -0400, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote:
>|||May be optimizer is not using the right indexes, you can try forcing the ind
ex.
Mohammed.
"Andrew J. Kelly" wrote:
> Are you sure there were no indexes added to the remote machine between the
> time the backup was created and now? If the stats are the same (try runni
ng
> in on the remote machine and see if it changes) then they should be the sa
me
> plan. Can you post the code for the sp?
> --
> Andrew J. Kelly SQL MVP
>
> "larzeb" <larzeb@.community.nospam> wrote in message
> news:bg46k11u0j542q5ua7dddol0g2fqfi58jt@.
4ax.com...
>
>|||You did run sp_updatestats in the correct database context not the default
for [master]?
i.e.
USE [MyDB]
GO
exec sp_updatestats
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"larzeb" <larzeb@.community.nospam> wrote in message
news:bg46k11u0j542q5ua7dddol0g2fqfi58jt@.
4ax.com...[vbcol=seagreen]
> Thanks for the reply, Andrew. I'm sorry to say it had no effect.
> On Tue, 4 Oct 2005 18:28:55 -0400, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote:
>|||Andrew,
Sorry for the delay - DSL down for over 24 hours.
No changes made to anything execpt restoring DB, syncying user ids from Mast
er, and running the
sp_updatestats. I also went to the remote, production machine and copied the
BAK file to DVD, rather
than use the compressed BAK. The results were the same.
The SP and associated table definitions follow. I abridged some of the table
s for simplicity.
exec usp_Mailings_Ins @.MailCampaignDetailID = 68, @.AddressID = 1639896, @.Per
sonID = 1659603,
@.BatchID = 411, @.TrayNo = 1, @.SerialNo = 3, @.NextMailDate = 'Dec 31 2100 12:
00AM', @.OEL =
N'****************AUTO**5-DIGIT 90210', @.MailID = @.P1 output
select @.P1
CREATE PROCEDURE [dbo].usp_Mailings_Ins
@.MailCampaignDetailID int,
@.AddressID int,
@.PersonID int,
@.BatchID int,
@.TrayNo int,
@.SerialNo int,
@.NextMailDate DATETIME,
@.OEL varchar(50),
@.MailID int OUTPUT
AS
INSERT INTO [dbo].[Mailings] (
[MailCampaignDetailID],
[AddressID],
[PersonID],
[BatchID],
[TrayNo],
[SerialNo],
NextMailDate,
OEL
) VALUES (
@.MailCampaignDetailID,
@.AddressID,
@.PersonID,
@.BatchID,
@.TrayNo,
@.SerialNo,
@.NextMailDate,
@.OEL
)
SET @.MailID = SCOPE_IDENTITY()
GO
CREATE TABLE [dbo].[MailingBatch] (
[BatchID] [int] IDENTITY (1, 1) NOT NULL ,
[CompID] [int] NOT NULL ,
[MailCampaignDetailID] [int] NOT NULL ,
[BatchType] [int] NOT NULL ,
[PostageType] [int] NULL ,
[Cost] [decimal](18, 0) NOT NULL ,
[parameters] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT NULL ,
[dateAdded] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[MailCampaignDetail] (
[MailCampaignDetailID] [int] IDENTITY (1, 1) NOT NULL ,
[MailCampaignID] [int] NOT NULL ,
..
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[AddressValid] (
[AddressID] [int] NOT NULL ,
..
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Person] (
[PersonID] [int] IDENTITY (1, 1) NOT NULL ,
[AddressID] [int] NOT NULL ,
..
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Mailings] (
[MailID] [int] IDENTITY (1, 1) NOT NULL ,
[MailCampaignDetailID] [int] NOT NULL ,
[AddressID] [int] NOT NULL ,
[PersonID] [int] NOT NULL ,
[BatchID] [int] NULL ,
[TrayNo] [int] NULL ,
[SerialNo] [int] NULL ,
[NextMailDate] [datetime] NULL ,
[MailDate] [smalldatetime] NOT NULL ,
[OEL] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MailingBatch] WITH NOCHECK ADD
CONSTRAINT [PK_Batch] PRIMARY KEY CLUSTERED
(
[BatchID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MailCampaignDetail] WITH NOCHECK ADD
CONSTRAINT [PK_MailCampaignDetail] PRIMARY KEY CLUSTERED
(
[MailCampaignDetailID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AddressValid] WITH NOCHECK ADD
CONSTRAINT [PK_AddressValid] PRIMARY KEY CLUSTERED
(
[AddressID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Person] WITH NOCHECK ADD
CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
(
[PersonID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Mailings] WITH NOCHECK ADD
CONSTRAINT [PK_Mailings] PRIMARY KEY CLUSTERED
(
[MailID]
) ON [PRIMARY]
GO
CREATE INDEX [IX_Mailings_BatchID] ON [dbo].[Mailings]([Bat
chID], [SerialNo]) ON [PRIMARY]
GO
CREATE INDEX [IX_Mailings_PersonID] ON [dbo].[Mailings]([Pe
rsonID]) ON [PRIMARY]
GO
CREATE INDEX [IX_Mailings_MCDID_AddressID] ON [dbo].[Mailings](
[MailCampaignDetailID],
[AddressID]) ON [PRIMARY]
GO
CREATE INDEX [IX_Mailings_AddressID] ON [dbo].[Mailings]([A
ddressID]) ON [PRIMARY]
GO
CREATE INDEX [Mailings27] ON [dbo].[Mailings]([AddressID],
[MailCampaignDetailID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Mailings] ADD
CONSTRAINT [FK_Mailings_AddressValid] FOREIGN KEY
(
[AddressID]
) REFERENCES [dbo].[AddressValid] (
[AddressID]
),
CONSTRAINT [FK_Mailings_MCD] FOREIGN KEY
(
[MailCampaignDetailID]
) REFERENCES [dbo].[MailCampaignDetail] (
[MailCampaignDetailID]
),
CONSTRAINT [FK_Mailings_Person] FOREIGN KEY
(
[PersonID]
) REFERENCES [dbo].[Person] (
[PersonID]
)
GO
On Tue, 4 Oct 2005 19:54:56 -0400, "Andrew J. Kelly" <sqlmvpnooospam@.shadhaw
k.com> wrote:
>Are you sure there were no indexes added to the remote machine between the
>time the backup was created and now? If the stats are the same (try runnin
g
>in on the remote machine and see if it changes) then they should be the sam
e
>plan. Can you post the code for the sp?|||I was sure I ran sp_updatestats against the appropriate DB.
On Wed, 05 Oct 2005 06:13:44 GMT, "Nik Marshall-Blank" <Nik@.here.com> wrote:
>You did run sp_updatestats in the correct database context not the default
>for [master]?
>i.e.
>USE [MyDB]
>GO
>exec sp_updatestats|||Mohammed,
I'm afraid I don't know how to "force" an index. Can you give me an example?
On Tue, 4 Oct 2005 18:08:03 -0700, "Mohammed" <Mohammed@.discussions.microsof
t.com> wrote:
[vbcol=seagreen]
>May be optimizer is not using the right indexes, you can try forcing the in
dex.
>Mohammed.
>"Andrew J. Kelly" wrote:
>|||Well one thing I see is that you should put SET NOCOUNT ON at the beginning
of your sp but that should be the same on both.
Andrew J. Kelly SQL MVP
"larzeb" <larzeb@.community.nospam> wrote in message
news:fs4bk1de83u04keblbk0qnnr0hit4fvtae@.
4ax.com...[vbcol=seagreen]
> Andrew,
> Sorry for the delay - DSL down for over 24 hours.
> No changes made to anything execpt restoring DB, syncying user ids from
> Master, and running the
> sp_updatestats. I also went to the remote, production machine and copied
> the BAK file to DVD, rather
> than use the compressed BAK. The results were the same.
> The SP and associated table definitions follow. I abridged some of the
> tables for simplicity.
> exec usp_Mailings_Ins @.MailCampaignDetailID = 68, @.AddressID = 1639896,
> @.PersonID = 1659603,
> @.BatchID = 411, @.TrayNo = 1, @.SerialNo = 3, @.NextMailDate = 'Dec 31 2100
> 12:00AM', @.OEL =
> N'****************AUTO**5-DIGIT 90210', @.MailID = @.P1 output
> select @.P1
> CREATE PROCEDURE [dbo].usp_Mailings_Ins
> @.MailCampaignDetailID int,
> @.AddressID int,
> @.PersonID int,
> @.BatchID int,
> @.TrayNo int,
> @.SerialNo int,
> @.NextMailDate DATETIME,
> @.OEL varchar(50),
> @.MailID int OUTPUT
> AS
> INSERT INTO [dbo].[Mailings] (
> [MailCampaignDetailID],
> [AddressID],
> [PersonID],
> [BatchID],
> [TrayNo],
> [SerialNo],
> NextMailDate,
> OEL
> ) VALUES (
> @.MailCampaignDetailID,
> @.AddressID,
> @.PersonID,
> @.BatchID,
> @.TrayNo,
> @.SerialNo,
> @.NextMailDate,
> @.OEL
> )
> SET @.MailID = SCOPE_IDENTITY()
> GO
> CREATE TABLE [dbo].[MailingBatch] (
> [BatchID] [int] IDENTITY (1, 1) NOT NULL ,
> [CompID] [int] NOT NULL ,
> [MailCampaignDetailID] [int] NOT NULL ,
> [BatchType] [int] NOT NULL ,
> [PostageType] [int] NULL ,
> [Cost] [decimal](18, 0) NOT NULL ,
> [parameters] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT
> NULL ,
> [dateAdded] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[MailCampaignDetail] (
> [MailCampaignDetailID] [int] IDENTITY (1, 1) NOT NULL ,
> [MailCampaignID] [int] NOT NULL ,
> ...
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[AddressValid] (
> [AddressID] [int] NOT NULL ,
> ...
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Person] (
> [PersonID] [int] IDENTITY (1, 1) NOT NULL ,
> [AddressID] [int] NOT NULL ,
> ...
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Mailings] (
> [MailID] [int] IDENTITY (1, 1) NOT NULL ,
> [MailCampaignDetailID] [int] NOT NULL ,
> [AddressID] [int] NOT NULL ,
> [PersonID] [int] NOT NULL ,
> [BatchID] [int] NULL ,
> [TrayNo] [int] NULL ,
> [SerialNo] [int] NULL ,
> [NextMailDate] [datetime] NULL ,
> [MailDate] [smalldatetime] NOT NULL ,
> [OEL] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[MailingBatch] WITH NOCHECK ADD
> CONSTRAINT [PK_Batch] PRIMARY KEY CLUSTERED
> (
> [BatchID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[MailCampaignDetail] WITH NOCHECK ADD
> CONSTRAINT [PK_MailCampaignDetail] PRIMARY KEY CLUSTERED
> (
> [MailCampaignDetailID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[AddressValid] WITH NOCHECK ADD
> CONSTRAINT [PK_AddressValid] PRIMARY KEY CLUSTERED
> (
> [AddressID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Person] WITH NOCHECK ADD
> CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
> (
> [PersonID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Mailings] WITH NOCHECK ADD
> CONSTRAINT [PK_Mailings] PRIMARY KEY CLUSTERED
> (
> [MailID]
> ) ON [PRIMARY]
> GO
> CREATE INDEX [IX_Mailings_BatchID] ON [dbo].[Mailings]([B
atchID],
> [SerialNo]) ON [PRIMARY]
> GO
> CREATE INDEX [IX_Mailings_PersonID] ON [dbo].[Mailings]([
PersonID]) ON
> [PRIMARY]
> GO
> CREATE INDEX [IX_Mailings_MCDID_AddressID] ON
> [dbo].[Mailings]([MailCampaignDetailID],
> [AddressID]) ON [PRIMARY]
> GO
> CREATE INDEX [IX_Mailings_AddressID] ON [dbo].[Mailings]([
;AddressID]) ON
> [PRIMARY]
> GO
> CREATE INDEX [Mailings27] ON [dbo].[Mailings]([AddressID]
,
> [MailCampaignDetailID]) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Mailings] ADD
> CONSTRAINT [FK_Mailings_AddressValid] FOREIGN KEY
> (
> [AddressID]
> ) REFERENCES [dbo].[AddressValid] (
> [AddressID]
> ),
> CONSTRAINT [FK_Mailings_MCD] FOREIGN KEY
> (
> [MailCampaignDetailID]
> ) REFERENCES [dbo].[MailCampaignDetail] (
> [MailCampaignDetailID]
> ),
> CONSTRAINT [FK_Mailings_Person] FOREIGN KEY
> (
> [PersonID]
> ) REFERENCES [dbo].[Person] (
> [PersonID]
> )
> GO
>
> On Tue, 4 Oct 2005 19:54:56 -0400, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote:
>
Bizarre performance hit
development machine.
The remote machine is a Intel P4, 3.20GHz + 1G. The development machine is an AMD X2 3800+ dual
processor with 2G memory. Subjectively, I feel the development machine is noticeably faster in most
ways.
On the remote machine, I execute a SP which inserts approximately 6000 records. From the profiler, I
see each insertion takes about 50 Reads and about 0 Duration, which is acceptable.
After synchronizing the database logins with the those in the Master table on the development
machine, I execute the same SP on the development machine. The Reads are about 7500 and the Duration
around 3200!
Can anyone suggest reasons why this differential might occur? I really do need help. I'm not a DBA
but a developer. But it doesn't take a DBA to know that this won't fly.
TIARun sp_updatestats after a restore and then try it.
--
Andrew J. Kelly SQL MVP
"larzeb" <larzeb@.community.nospam> wrote in message
news:5os5k15iub07uqubpdqb8acgg7vcaq0grj@.4ax.com...
>I create backups of a remote, production database and copy it after
>compression to a local,
> development machine.
> The remote machine is a Intel P4, 3.20GHz + 1G. The development machine is
> an AMD X2 3800+ dual
> processor with 2G memory. Subjectively, I feel the development machine is
> noticeably faster in most
> ways.
> On the remote machine, I execute a SP which inserts approximately 6000
> records. From the profiler, I
> see each insertion takes about 50 Reads and about 0 Duration, which is
> acceptable.
> After synchronizing the database logins with the those in the Master table
> on the development
> machine, I execute the same SP on the development machine. The Reads are
> about 7500 and the Duration
> around 3200!
> Can anyone suggest reasons why this differential might occur? I really do
> need help. I'm not a DBA
> but a developer. But it doesn't take a DBA to know that this won't fly.
> TIA|||Thanks for the reply, Andrew. I'm sorry to say it had no effect.
On Tue, 4 Oct 2005 18:28:55 -0400, "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote:
>Run sp_updatestats after a restore and then try it.|||Are you sure there were no indexes added to the remote machine between the
time the backup was created and now? If the stats are the same (try running
in on the remote machine and see if it changes) then they should be the same
plan. Can you post the code for the sp?
--
Andrew J. Kelly SQL MVP
"larzeb" <larzeb@.community.nospam> wrote in message
news:bg46k11u0j542q5ua7dddol0g2fqfi58jt@.4ax.com...
> Thanks for the reply, Andrew. I'm sorry to say it had no effect.
> On Tue, 4 Oct 2005 18:28:55 -0400, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote:
>>Run sp_updatestats after a restore and then try it.|||May be optimizer is not using the right indexes, you can try forcing the index.
Mohammed.
"Andrew J. Kelly" wrote:
> Are you sure there were no indexes added to the remote machine between the
> time the backup was created and now? If the stats are the same (try running
> in on the remote machine and see if it changes) then they should be the same
> plan. Can you post the code for the sp?
> --
> Andrew J. Kelly SQL MVP
>
> "larzeb" <larzeb@.community.nospam> wrote in message
> news:bg46k11u0j542q5ua7dddol0g2fqfi58jt@.4ax.com...
> > Thanks for the reply, Andrew. I'm sorry to say it had no effect.
> >
> > On Tue, 4 Oct 2005 18:28:55 -0400, "Andrew J. Kelly"
> > <sqlmvpnooospam@.shadhawk.com> wrote:
> >
> >>Run sp_updatestats after a restore and then try it.
>
>|||You did run sp_updatestats in the correct database context not the default
for [master]?
i.e.
USE [MyDB]
GO
exec sp_updatestats
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"larzeb" <larzeb@.community.nospam> wrote in message
news:bg46k11u0j542q5ua7dddol0g2fqfi58jt@.4ax.com...
> Thanks for the reply, Andrew. I'm sorry to say it had no effect.
> On Tue, 4 Oct 2005 18:28:55 -0400, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote:
>>Run sp_updatestats after a restore and then try it.|||Andrew,
Sorry for the delay - DSL down for over 24 hours.
No changes made to anything execpt restoring DB, syncying user ids from Master, and running the
sp_updatestats. I also went to the remote, production machine and copied the BAK file to DVD, rather
than use the compressed BAK. The results were the same.
The SP and associated table definitions follow. I abridged some of the tables for simplicity.
exec usp_Mailings_Ins @.MailCampaignDetailID = 68, @.AddressID = 1639896, @.PersonID = 1659603,
@.BatchID = 411, @.TrayNo = 1, @.SerialNo = 3, @.NextMailDate = 'Dec 31 2100 12:00AM', @.OEL =N'****************AUTO**5-DIGIT 90210', @.MailID = @.P1 output
select @.P1
CREATE PROCEDURE [dbo].usp_Mailings_Ins
@.MailCampaignDetailID int,
@.AddressID int,
@.PersonID int,
@.BatchID int,
@.TrayNo int,
@.SerialNo int,
@.NextMailDate DATETIME,
@.OEL varchar(50),
@.MailID int OUTPUT
AS
INSERT INTO [dbo].[Mailings] (
[MailCampaignDetailID],
[AddressID],
[PersonID],
[BatchID],
[TrayNo],
[SerialNo],
NextMailDate,
OEL
) VALUES (
@.MailCampaignDetailID,
@.AddressID,
@.PersonID,
@.BatchID,
@.TrayNo,
@.SerialNo,
@.NextMailDate,
@.OEL
)
SET @.MailID = SCOPE_IDENTITY()
GO
CREATE TABLE [dbo].[MailingBatch] (
[BatchID] [int] IDENTITY (1, 1) NOT NULL ,
[CompID] [int] NOT NULL ,
[MailCampaignDetailID] [int] NOT NULL ,
[BatchType] [int] NOT NULL ,
[PostageType] [int] NULL ,
[Cost] [decimal](18, 0) NOT NULL ,
[parameters] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[dateAdded] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[MailCampaignDetail] (
[MailCampaignDetailID] [int] IDENTITY (1, 1) NOT NULL ,
[MailCampaignID] [int] NOT NULL ,
...
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[AddressValid] (
[AddressID] [int] NOT NULL ,
...
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Person] (
[PersonID] [int] IDENTITY (1, 1) NOT NULL ,
[AddressID] [int] NOT NULL ,
...
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Mailings] (
[MailID] [int] IDENTITY (1, 1) NOT NULL ,
[MailCampaignDetailID] [int] NOT NULL ,
[AddressID] [int] NOT NULL ,
[PersonID] [int] NOT NULL ,
[BatchID] [int] NULL ,
[TrayNo] [int] NULL ,
[SerialNo] [int] NULL ,
[NextMailDate] [datetime] NULL ,
[MailDate] [smalldatetime] NOT NULL ,
[OEL] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MailingBatch] WITH NOCHECK ADD
CONSTRAINT [PK_Batch] PRIMARY KEY CLUSTERED
(
[BatchID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MailCampaignDetail] WITH NOCHECK ADD
CONSTRAINT [PK_MailCampaignDetail] PRIMARY KEY CLUSTERED
(
[MailCampaignDetailID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AddressValid] WITH NOCHECK ADD
CONSTRAINT [PK_AddressValid] PRIMARY KEY CLUSTERED
(
[AddressID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Person] WITH NOCHECK ADD
CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
(
[PersonID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Mailings] WITH NOCHECK ADD
CONSTRAINT [PK_Mailings] PRIMARY KEY CLUSTERED
(
[MailID]
) ON [PRIMARY]
GO
CREATE INDEX [IX_Mailings_BatchID] ON [dbo].[Mailings]([BatchID], [SerialNo]) ON [PRIMARY]
GO
CREATE INDEX [IX_Mailings_PersonID] ON [dbo].[Mailings]([PersonID]) ON [PRIMARY]
GO
CREATE INDEX [IX_Mailings_MCDID_AddressID] ON [dbo].[Mailings]([MailCampaignDetailID],
[AddressID]) ON [PRIMARY]
GO
CREATE INDEX [IX_Mailings_AddressID] ON [dbo].[Mailings]([AddressID]) ON [PRIMARY]
GO
CREATE INDEX [Mailings27] ON [dbo].[Mailings]([AddressID], [MailCampaignDetailID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Mailings] ADD
CONSTRAINT [FK_Mailings_AddressValid] FOREIGN KEY
(
[AddressID]
) REFERENCES [dbo].[AddressValid] (
[AddressID]
),
CONSTRAINT [FK_Mailings_MCD] FOREIGN KEY
(
[MailCampaignDetailID]
) REFERENCES [dbo].[MailCampaignDetail] (
[MailCampaignDetailID]
),
CONSTRAINT [FK_Mailings_Person] FOREIGN KEY
(
[PersonID]
) REFERENCES [dbo].[Person] (
[PersonID]
)
GO
On Tue, 4 Oct 2005 19:54:56 -0400, "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote:
>Are you sure there were no indexes added to the remote machine between the
>time the backup was created and now? If the stats are the same (try running
>in on the remote machine and see if it changes) then they should be the same
>plan. Can you post the code for the sp?|||I was sure I ran sp_updatestats against the appropriate DB.
On Wed, 05 Oct 2005 06:13:44 GMT, "Nik Marshall-Blank" <Nik@.here.com> wrote:
>You did run sp_updatestats in the correct database context not the default
>for [master]?
>i.e.
>USE [MyDB]
>GO
>exec sp_updatestats|||Mohammed,
I'm afraid I don't know how to "force" an index. Can you give me an example?
On Tue, 4 Oct 2005 18:08:03 -0700, "Mohammed" <Mohammed@.discussions.microsoft.com> wrote:
>May be optimizer is not using the right indexes, you can try forcing the index.
>Mohammed.
>"Andrew J. Kelly" wrote:
>> Are you sure there were no indexes added to the remote machine between the
>> time the backup was created and now? If the stats are the same (try running
>> in on the remote machine and see if it changes) then they should be the same
>> plan. Can you post the code for the sp?
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "larzeb" <larzeb@.community.nospam> wrote in message
>> news:bg46k11u0j542q5ua7dddol0g2fqfi58jt@.4ax.com...
>> > Thanks for the reply, Andrew. I'm sorry to say it had no effect.
>> >
>> > On Tue, 4 Oct 2005 18:28:55 -0400, "Andrew J. Kelly"
>> > <sqlmvpnooospam@.shadhawk.com> wrote:
>> >
>> >>Run sp_updatestats after a restore and then try it.
>>|||Well one thing I see is that you should put SET NOCOUNT ON at the beginning
of your sp but that should be the same on both.
Andrew J. Kelly SQL MVP
"larzeb" <larzeb@.community.nospam> wrote in message
news:fs4bk1de83u04keblbk0qnnr0hit4fvtae@.4ax.com...
> Andrew,
> Sorry for the delay - DSL down for over 24 hours.
> No changes made to anything execpt restoring DB, syncying user ids from
> Master, and running the
> sp_updatestats. I also went to the remote, production machine and copied
> the BAK file to DVD, rather
> than use the compressed BAK. The results were the same.
> The SP and associated table definitions follow. I abridged some of the
> tables for simplicity.
> exec usp_Mailings_Ins @.MailCampaignDetailID = 68, @.AddressID = 1639896,
> @.PersonID = 1659603,
> @.BatchID = 411, @.TrayNo = 1, @.SerialNo = 3, @.NextMailDate = 'Dec 31 2100
> 12:00AM', @.OEL => N'****************AUTO**5-DIGIT 90210', @.MailID = @.P1 output
> select @.P1
> CREATE PROCEDURE [dbo].usp_Mailings_Ins
> @.MailCampaignDetailID int,
> @.AddressID int,
> @.PersonID int,
> @.BatchID int,
> @.TrayNo int,
> @.SerialNo int,
> @.NextMailDate DATETIME,
> @.OEL varchar(50),
> @.MailID int OUTPUT
> AS
> INSERT INTO [dbo].[Mailings] (
> [MailCampaignDetailID],
> [AddressID],
> [PersonID],
> [BatchID],
> [TrayNo],
> [SerialNo],
> NextMailDate,
> OEL
> ) VALUES (
> @.MailCampaignDetailID,
> @.AddressID,
> @.PersonID,
> @.BatchID,
> @.TrayNo,
> @.SerialNo,
> @.NextMailDate,
> @.OEL
> )
> SET @.MailID = SCOPE_IDENTITY()
> GO
> CREATE TABLE [dbo].[MailingBatch] (
> [BatchID] [int] IDENTITY (1, 1) NOT NULL ,
> [CompID] [int] NOT NULL ,
> [MailCampaignDetailID] [int] NOT NULL ,
> [BatchType] [int] NOT NULL ,
> [PostageType] [int] NULL ,
> [Cost] [decimal](18, 0) NOT NULL ,
> [parameters] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [dateAdded] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[MailCampaignDetail] (
> [MailCampaignDetailID] [int] IDENTITY (1, 1) NOT NULL ,
> [MailCampaignID] [int] NOT NULL ,
> ...
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[AddressValid] (
> [AddressID] [int] NOT NULL ,
> ...
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Person] (
> [PersonID] [int] IDENTITY (1, 1) NOT NULL ,
> [AddressID] [int] NOT NULL ,
> ...
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Mailings] (
> [MailID] [int] IDENTITY (1, 1) NOT NULL ,
> [MailCampaignDetailID] [int] NOT NULL ,
> [AddressID] [int] NOT NULL ,
> [PersonID] [int] NOT NULL ,
> [BatchID] [int] NULL ,
> [TrayNo] [int] NULL ,
> [SerialNo] [int] NULL ,
> [NextMailDate] [datetime] NULL ,
> [MailDate] [smalldatetime] NOT NULL ,
> [OEL] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[MailingBatch] WITH NOCHECK ADD
> CONSTRAINT [PK_Batch] PRIMARY KEY CLUSTERED
> (
> [BatchID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[MailCampaignDetail] WITH NOCHECK ADD
> CONSTRAINT [PK_MailCampaignDetail] PRIMARY KEY CLUSTERED
> (
> [MailCampaignDetailID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[AddressValid] WITH NOCHECK ADD
> CONSTRAINT [PK_AddressValid] PRIMARY KEY CLUSTERED
> (
> [AddressID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Person] WITH NOCHECK ADD
> CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
> (
> [PersonID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Mailings] WITH NOCHECK ADD
> CONSTRAINT [PK_Mailings] PRIMARY KEY CLUSTERED
> (
> [MailID]
> ) ON [PRIMARY]
> GO
> CREATE INDEX [IX_Mailings_BatchID] ON [dbo].[Mailings]([BatchID],
> [SerialNo]) ON [PRIMARY]
> GO
> CREATE INDEX [IX_Mailings_PersonID] ON [dbo].[Mailings]([PersonID]) ON
> [PRIMARY]
> GO
> CREATE INDEX [IX_Mailings_MCDID_AddressID] ON
> [dbo].[Mailings]([MailCampaignDetailID],
> [AddressID]) ON [PRIMARY]
> GO
> CREATE INDEX [IX_Mailings_AddressID] ON [dbo].[Mailings]([AddressID]) ON
> [PRIMARY]
> GO
> CREATE INDEX [Mailings27] ON [dbo].[Mailings]([AddressID],
> [MailCampaignDetailID]) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Mailings] ADD
> CONSTRAINT [FK_Mailings_AddressValid] FOREIGN KEY
> (
> [AddressID]
> ) REFERENCES [dbo].[AddressValid] (
> [AddressID]
> ),
> CONSTRAINT [FK_Mailings_MCD] FOREIGN KEY
> (
> [MailCampaignDetailID]
> ) REFERENCES [dbo].[MailCampaignDetail] (
> [MailCampaignDetailID]
> ),
> CONSTRAINT [FK_Mailings_Person] FOREIGN KEY
> (
> [PersonID]
> ) REFERENCES [dbo].[Person] (
> [PersonID]
> )
> GO
>
> On Tue, 4 Oct 2005 19:54:56 -0400, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote:
>>Are you sure there were no indexes added to the remote machine between the
>>time the backup was created and now? If the stats are the same (try
>>running
>>in on the remote machine and see if it changes) then they should be the
>>same
>>plan. Can you post the code for the sp?|||I began comparing the execution plans on each of the two machines. Of course, they were different.
One big difference was a Hash Match/Inner Join which has an estimated row count of 2,250,000 on the
development machine.
The execution plan says that it is doing an Index Scan on Addressvalid.Address21 and also on
UseCode.IX_UseCode which feeds the Hash Match/Inner Join:
|--Hash Match(Inner Join, HASH:([UseCode].[UseCodeID],
[UseCode].[CountyFipsID])=([AddressValid].[useCodeID], [AddressValid].[countyCodeFips]),
RESIDUAL:([AddressValid].[useCodeID]=[UseCode].[UseCodeID] AND
[AddressValid].[countyCodeFips]=[UseCode].[CountyFipsID]
|--Index Scan(OBJECT:([MailHouse].[dbo].[UseCode].[IX_UseCode]))
|--Index Scan(OBJECT:([MailHouse].[dbo].[AddressValid].[AddressValid21]))
Why is doing all this when it's supposed to be inserting rows in Mailings?
Why is the development machine doing this and the production not?
How can I get to two machines in sync?
Thanks for you help.
CREATE TABLE [dbo].[AddressValid] (
[AddressID] [int] NOT NULL,
[dataSourceID] [int] NOT NULL,
...
[useCodeID] [char] (6) NOT NULL,
...
[houseNo] [varchar] (10) NULL,
[preDir] [char] (2) NULL,
[streetName] [varchar] (28) NULL,
[streetSuffix] [char] (4) NULL,
[postDir] [char] (2) NULL,
[city] [varchar] (28) NULL,
[state] [char] (2) NULL,
[zip5] [char] (5) NULL,
[zip4] [char] (4) NULL,
[sud] [char] (4) NULL,
[unitNum] [varchar] (8) NULL,
...
[countyCodeFips] [char] (5) NULL,
...
[dateAdded] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AddressValid] WITH NOCHECK ADD
CONSTRAINT [PK_AddressValid] PRIMARY KEY CLUSTERED
(
[AddressID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AddressValid] WITH NOCHECK ADD
CONSTRAINT [IX_AddressValid] UNIQUE NONCLUSTERED
(
[streetName],
[houseNo],
[streetSuffix],
[preDir],
[postDir],
[zip5],
[zip4],
[sud],
[unitNum]
) ON [PRIMARY]
GO
CREATE INDEX [IX_Zip5] ON [dbo].[AddressValid]([zip5]) ON [PRIMARY]
GO
CREATE INDEX [AddressValid21] ON [dbo].[AddressValid]([AddressID], [useCodeID], [zip5],
[countyCodeFips]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AddressValid] ADD
CONSTRAINT [FK_AddressValid_AddressSource] FOREIGN KEY
(
[AddressID]
) REFERENCES [dbo].[AddressSource] (
[addressID]
),
CONSTRAINT [FK_AddressValid_datasource] FOREIGN KEY
(
[dataSourceID]
) REFERENCES [dbo].[datasource] (
[DataSourceID]
),
CONSTRAINT [FK_AddressValid_UseCode] FOREIGN KEY
(
[countyCodeFips],
[useCodeID]
) REFERENCES [dbo].[UseCode] (
[CountyFipsID],
[UseCodeID]
)
GO
CREATE TABLE [dbo].[UseCode] (
[UseCodePK] [int] IDENTITY (1, 1) NOT NULL,
[CountyFipsID] [char] (5) NOT NULL,
[UseCodeID] [char] (6) NOT NULL,
[descr] [varchar] (50) NOT NULL,
[dateAdded] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[UseCode] WITH NOCHECK ADD
CONSTRAINT [PK_UseCode] PRIMARY KEY CLUSTERED
(
[UseCodePK]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[UseCode] WITH NOCHECK ADD
CONSTRAINT [IX_UseCode] UNIQUE NONCLUSTERED
(
[CountyFipsID],
[UseCodeID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[UseCode] ADD
CONSTRAINT [FK_UseCode_CountyFips] FOREIGN KEY
(
[CountyFipsID]
) REFERENCES [dbo].[CountyFips] (
[CountyFipsID]
)
GO
On Thu, 6 Oct 2005 18:14:28 -0400, "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote:
>Well one thing I see is that you should put SET NOCOUNT ON at the beginning
>of your sp but that should be the same on both.|||Hello,
I have tested the issue on my side but I am unable to reproduce the issue.
To narrow down the issue, I suggest that you perform the following steps:
1. Restore the database on another known working machine using the same
backup file. The machine has the similar hardware configuration as the
remote machine. Check if you can reproduce the issue on another machine.
2. Create a new test database. Create some tables and a SP in the test
database to check if you can reproduce the issue on another database. Let
me know the results.
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Sounds like you have referential integrity on some of the columns. In order
to enforce the RI it has to search the other tables. If they don't have
proper indexes it can be a real mess. If the schemas really are identical
(use a tool such as www.red-gate.com to verify) and the number of rows are
the same it would usually boil down to statistics being different. You say
you updated the ones on the dev server, what about the production?
--
Andrew J. Kelly SQL MVP
"larzeb" <larzeb@.community.nospam> wrote in message
news:58bbk11d1g82ein2issf0c878q6kjj6r6o@.4ax.com...
>I began comparing the execution plans on each of the two machines. Of
>course, they were different.
> One big difference was a Hash Match/Inner Join which has an estimated row
> count of 2,250,000 on the
> development machine.
> The execution plan says that it is doing an Index Scan on
> Addressvalid.Address21 and also on
> UseCode.IX_UseCode which feeds the Hash Match/Inner Join:
> |--Hash Match(Inner Join,
> HASH:([UseCode].[UseCodeID],
> [UseCode].[CountyFipsID])=([AddressValid].[useCodeID],
> [AddressValid].[countyCodeFips]),
> RESIDUAL:([AddressValid].[useCodeID]=[UseCode].[UseCodeID] AND
> [AddressValid].[countyCodeFips]=[UseCode].[CountyFipsID]
> |--Index
> Scan(OBJECT:([MailHouse].[dbo].[UseCode].[IX_UseCode]))
> |--Index
> Scan(OBJECT:([MailHouse].[dbo].[AddressValid].[AddressValid21]))
> Why is doing all this when it's supposed to be inserting rows in Mailings?
> Why is the development machine doing this and the production not?
> How can I get to two machines in sync?
> Thanks for you help.
> CREATE TABLE [dbo].[AddressValid] (
> [AddressID] [int] NOT NULL,
> [dataSourceID] [int] NOT NULL,
> ...
> [useCodeID] [char] (6) NOT NULL,
> ...
> [houseNo] [varchar] (10) NULL,
> [preDir] [char] (2) NULL,
> [streetName] [varchar] (28) NULL,
> [streetSuffix] [char] (4) NULL,
> [postDir] [char] (2) NULL,
> [city] [varchar] (28) NULL,
> [state] [char] (2) NULL,
> [zip5] [char] (5) NULL,
> [zip4] [char] (4) NULL,
> [sud] [char] (4) NULL,
> [unitNum] [varchar] (8) NULL,
> ...
> [countyCodeFips] [char] (5) NULL,
> ...
> [dateAdded] [smalldatetime] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[AddressValid] WITH NOCHECK ADD
> CONSTRAINT [PK_AddressValid] PRIMARY KEY CLUSTERED
> (
> [AddressID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[AddressValid] WITH NOCHECK ADD
> CONSTRAINT [IX_AddressValid] UNIQUE NONCLUSTERED
> (
> [streetName],
> [houseNo],
> [streetSuffix],
> [preDir],
> [postDir],
> [zip5],
> [zip4],
> [sud],
> [unitNum]
> ) ON [PRIMARY]
> GO
> CREATE INDEX [IX_Zip5] ON [dbo].[AddressValid]([zip5]) ON [PRIMARY]
> GO
> CREATE INDEX [AddressValid21] ON [dbo].[AddressValid]([AddressID],
> [useCodeID], [zip5],
> [countyCodeFips]) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[AddressValid] ADD
> CONSTRAINT [FK_AddressValid_AddressSource] FOREIGN KEY
> (
> [AddressID]
> ) REFERENCES [dbo].[AddressSource] (
> [addressID]
> ),
> CONSTRAINT [FK_AddressValid_datasource] FOREIGN KEY
> (
> [dataSourceID]
> ) REFERENCES [dbo].[datasource] (
> [DataSourceID]
> ),
> CONSTRAINT [FK_AddressValid_UseCode] FOREIGN KEY
> (
> [countyCodeFips],
> [useCodeID]
> ) REFERENCES [dbo].[UseCode] (
> [CountyFipsID],
> [UseCodeID]
> )
> GO
> CREATE TABLE [dbo].[UseCode] (
> [UseCodePK] [int] IDENTITY (1, 1) NOT NULL,
> [CountyFipsID] [char] (5) NOT NULL,
> [UseCodeID] [char] (6) NOT NULL,
> [descr] [varchar] (50) NOT NULL,
> [dateAdded] [smalldatetime] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[UseCode] WITH NOCHECK ADD
> CONSTRAINT [PK_UseCode] PRIMARY KEY CLUSTERED
> (
> [UseCodePK]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[UseCode] WITH NOCHECK ADD
> CONSTRAINT [IX_UseCode] UNIQUE NONCLUSTERED
> (
> [CountyFipsID],
> [UseCodeID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[UseCode] ADD
> CONSTRAINT [FK_UseCode_CountyFips] FOREIGN KEY
> (
> [CountyFipsID]
> ) REFERENCES [dbo].[CountyFips] (
> [CountyFipsID]
> )
> GO
>
> On Thu, 6 Oct 2005 18:14:28 -0400, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote:
>>Well one thing I see is that you should put SET NOCOUNT ON at the
>>beginning
>>of your sp but that should be the same on both.