Q Statistics & Query Performance

In the last couple of ws, my SQL server has been occasionally
bogging down during the day. I noticed that it was when a stored
procedure that usually takes 2 seconds to run timed out.
I ran sp_updatestats in Query Analyzer and everything went back to
normal.
AUTO_UPDATE_STATISTICS is set to ON in the database
As part of my database maintenance, I do the following:
- Update Statistics at 05:30am on wdays
- Perform a complete backup nightly at 03:00am
- Perform a transaction log backup every half an hour between 9am and
7pm on wdays
My first hunch is that I happened to be running the sp when the logs
were being backed up. However, the logs take less than a couple of
seconds to back up. Shouldn't things go back to normal after the
back-up is done?
The load on the server is pretty low, at most 10 users hitting the web
app front end at one time.
Any suggestions or tips? Thank You(george.durzi@.gmail.com) writes:
> In the last couple of ws, my SQL server has been occasionally
> bogging down during the day. I noticed that it was when a stored
> procedure that usually takes 2 seconds to run timed out.
> I ran sp_updatestats in Query Analyzer and everything went back to
> normal.
> AUTO_UPDATE_STATISTICS is set to ON in the database
> As part of my database maintenance, I do the following:
> - Update Statistics at 05:30am on wdays
> - Perform a complete backup nightly at 03:00am
> - Perform a transaction log backup every half an hour between 9am and
> 7pm on wdays
> My first hunch is that I happened to be running the sp when the logs
> were being backed up. However, the logs take less than a couple of
> seconds to back up. Shouldn't things go back to normal after the
> back-up is done?
> The load on the server is pretty low, at most 10 users hitting the web
> app front end at one time.
It's difficult to say with this little amount of information. But I
would guess that parameter sniffing is part of the plot. When SQL Server
builds the query plan for a stored procedure, it looks at the parameter
values, and uses these as guidance when building the plan. This means
that if the procedure is initially called with some odd value, you
may be stuck with a plan that is not good for regular values. Here
is a brief example:
CREATE PROCEDURE get_data @.last_key int = 0 AS
IF @.last_key = 0
SELECT ... FROM tbl
ELSE
SELECT ... FRON tbl WHERE keycol > @.last-key
Assume that this procedure is called in the morning to do an initial
load of a screen of some sort, and is then called repeatedly during
the day too update that screen. Assume further that the index on last_key
is non-clustered. If there is no plan cached in the morning, the optimzer
will use a table scan for both cases, as the NC index is not good
for reading all values.
When you run sp_updatestats, the optimizer might notice that statistcs
have changed and recompile the procedure with the currently value,
for which the index is useful.
This is a bit of speculation on my part. You might be able to get some
more cluse, if you start to inspect query plans. You can also use
DBCC SHOW_STATISTCS before and after to see whether are any significant
changes in statistics.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland,
Thanks for your tip, I'll look into this further

Q on joining tables with nullable fields

Question.
I have a new table that I am adding to a script that I wrote. This
table has 3 fields, the first 2 fields are used in the on statement as
being = other fields in the script.

The first field always has data in it, but the 2nd field is sometimes
null.

So my problem is if both fields have data in them and they both match
to the data in the fields that I am linking them to, then it returns
the 3rd field without a problem. However if the 2nd field is null then
it is returning a null for the 3rd field. I have checked and the field
that I am linking to is null also.

So if I have

select t1.field1, t1.field2, t2.field1, t2.field2, t2.field3
from table1 t1
join table2 t2
on t1.field1=t2.field1 and t1.field2=t2.field2

with 2 records in each table
table1: record1: data, data
record2: data, null
table2: record1: data,data,data
record2: data,null,data

what I get from the script is
record1: data, data,data,data,data
record2: data,null,data,null,null

I would expect
record2: data,null,data,null,data

I hope this makes sense, I didn't want to post the entire actual script
as it is about 150 lines long.

Thanks in advance.mike wrote:

Quote:

Originally Posted by

I have a new table that I am adding to a script that I wrote. This
table has 3 fields, the first 2 fields are used in the on statement as
being = other fields in the script.
>
The first field always has data in it, but the 2nd field is sometimes
null.
>
So my problem is if both fields have data in them and they both match
to the data in the fields that I am linking them to, then it returns
the 3rd field without a problem. However if the 2nd field is null then
it is returning a null for the 3rd field. I have checked and the field
that I am linking to is null also.
>
So if I have
>
select t1.field1, t1.field2, t2.field1, t2.field2, t2.field3
from table1 t1
join table2 t2
on t1.field1=t2.field1 and t1.field2=t2.field2
>
with 2 records in each table
table1: record1: data, data
record2: data, null
table2: record1: data,data,data
record2: data,null,data
>
what I get from the script is
record1: data, data,data,data,data
record2: data,null,data,null,null
>
>
I would expect
record2: data,null,data,null,data

Please use sample data like 'A', 'B', 'C', etc., instead of "data" which
is much more confusing.

Run some sanity checks on the data:

select * from table1 where field2 is null
select * from table1 where field2 = 'NULL'

select * from table2 where field2 is null
select * from table2 where field2 = 'NULL'

What tool are you using to pull the data? Some clients (e.g. Crystal
Reports) have a "convert nulls to empty values" option.|||Yes using select statements like that does return the data where field2
is null.
This is just using query analyzer. This is part of a view that I am
making some changes to.
Also I have tried using the SET ANSI_NULLS ON and SET ANSI_NULLS OFF
and it made no difference.

Ed Murphy wrote:

Quote:

Originally Posted by

mike wrote:
>

Quote:

Originally Posted by

I have a new table that I am adding to a script that I wrote. This
table has 3 fields, the first 2 fields are used in the on statement as
being = other fields in the script.

The first field always has data in it, but the 2nd field is sometimes
null.

So my problem is if both fields have data in them and they both match
to the data in the fields that I am linking them to, then it returns
the 3rd field without a problem. However if the 2nd field is null then
it is returning a null for the 3rd field. I have checked and the field
that I am linking to is null also.

So if I have

select t1.field1, t1.field2, t2.field1, t2.field2, t2.field3
from table1 t1
join table2 t2
on t1.field1=t2.field1 and t1.field2=t2.field2

with 2 records in each table
table1: record1: data, data
record2: data, null
table2: record1: data,data,data
record2: data,null,data

what I get from the script is
record1: data, data,data,data,data
record2: data,null,data,null,null

I would expect
record2: data,null,data,null,data

>
Please use sample data like 'A', 'B', 'C', etc., instead of "data" which
is much more confusing.
>
Run some sanity checks on the data:
>
select * from table1 where field2 is null
select * from table1 where field2 = 'NULL'
>
select * from table2 where field2 is null
select * from table2 where field2 = 'NULL'
>
What tool are you using to pull the data? Some clients (e.g. Crystal
Reports) have a "convert nulls to empty values" option.

|||Oh yea, and one thing I forgot is that my join is a Left Outer, if I
make it an inner join the records with the NULL just don't show at all.
So the issue is within the linking.

mike wrote:

Quote:

Originally Posted by

Yes using select statements like that does return the data where field2
is null.
This is just using query analyzer. This is part of a view that I am
making some changes to.
Also I have tried using the SET ANSI_NULLS ON and SET ANSI_NULLS OFF
and it made no difference.
>
Ed Murphy wrote:

Quote:

Originally Posted by

mike wrote:

Quote:

Originally Posted by

I have a new table that I am adding to a script that I wrote. This
table has 3 fields, the first 2 fields are used in the on statement as
being = other fields in the script.
>
The first field always has data in it, but the 2nd field is sometimes
null.
>
So my problem is if both fields have data in them and they both match
to the data in the fields that I am linking them to, then it returns
the 3rd field without a problem. However if the 2nd field is null then
it is returning a null for the 3rd field. I have checked and the field
that I am linking to is null also.
>
So if I have
>
select t1.field1, t1.field2, t2.field1, t2.field2, t2.field3
from table1 t1
join table2 t2
on t1.field1=t2.field1 and t1.field2=t2.field2
>
with 2 records in each table
table1: record1: data, data
record2: data, null
table2: record1: data,data,data
record2: data,null,data
>
what I get from the script is
record1: data, data,data,data,data
record2: data,null,data,null,null
>
>
I would expect
record2: data,null,data,null,data

Please use sample data like 'A', 'B', 'C', etc., instead of "data" which
is much more confusing.

Run some sanity checks on the data:

select * from table1 where field2 is null
select * from table1 where field2 = 'NULL'

select * from table2 where field2 is null
select * from table2 where field2 = 'NULL'

What tool are you using to pull the data? Some clients (e.g. Crystal
Reports) have a "convert nulls to empty values" option.

|||Ok I finally got it. I still don't know why that didn't work. But
here is what I did instead and it worked.
On the join I changed it to the following.

On t1.field1=t2.field1 and (case when t1.field2 is null then '' else
t1.field2 end)=(case when t2.field2 is null then '' else t2.field2 end)

mike wrote:

Quote:

Originally Posted by

Oh yea, and one thing I forgot is that my join is a Left Outer, if I
make it an inner join the records with the NULL just don't show at all.
So the issue is within the linking.
>
mike wrote:

Quote:

Originally Posted by

Yes using select statements like that does return the data where field2
is null.
This is just using query analyzer. This is part of a view that I am
making some changes to.
Also I have tried using the SET ANSI_NULLS ON and SET ANSI_NULLS OFF
and it made no difference.

Ed Murphy wrote:

Quote:

Originally Posted by

mike wrote:
>
I have a new table that I am adding to a script that I wrote. This
table has 3 fields, the first 2 fields are used in the on statement as
being = other fields in the script.

The first field always has data in it, but the 2nd field is sometimes
null.

So my problem is if both fields have data in them and they both match
to the data in the fields that I am linking them to, then it returns
the 3rd field without a problem. However if the 2nd field is null then
it is returning a null for the 3rd field. I have checked and the field
that I am linking to is null also.

So if I have

select t1.field1, t1.field2, t2.field1, t2.field2, t2.field3
from table1 t1
join table2 t2
on t1.field1=t2.field1 and t1.field2=t2.field2

with 2 records in each table
table1: record1: data, data
record2: data, null
table2: record1: data,data,data
record2: data,null,data

what I get from the script is
record1: data, data,data,data,data
record2: data,null,data,null,null


I would expect
record2: data,null,data,null,data
>
Please use sample data like 'A', 'B', 'C', etc., instead of "data" which
is much more confusing.
>
Run some sanity checks on the data:
>
select * from table1 where field2 is null
select * from table1 where field2 = 'NULL'
>
select * from table2 where field2 is null
select * from table2 where field2 = 'NULL'
>
What tool are you using to pull the data? Some clients (e.g. Crystal
Reports) have a "convert nulls to empty values" option.

|||mike wrote:

Quote:

Originally Posted by

Ok I finally got it. I still don't know why that didn't work.

Nulls are not considered equal to anything, not even other nulls.

http://en.wikipedia.org/wiki/Null_(SQL)

Quote:

Originally Posted by

But here is what I did instead and it worked.
On the join I changed it to the following.
>
On t1.field1=t2.field1 and (case when t1.field2 is null then '' else
t1.field2 end)=(case when t2.field2 is null then '' else t2.field2 end)

Equivalent and shorter:

on t1.field1 = t2.field1
and coalesce(t1.field2,'') = coalesce(t2.field2,'')

coalesce() is a function that takes one or more argument and returns the
first non-null value among them, or null if they're all null.

Q on Clustered and Nonclustered Index files to be on Raid

I have SAN mapped as Raid 5 and Raid 0. I have to place Clustered Index file
(contains all clustered indexes) and Nonclustered Index file file (contains
all nonclustered indexes). Which Raid level is the best and recommended for
clustered and none cluster
ed index files to have a better performance? It is sql 2k.
Thank you,
KtfI would suggest that this is not enough information. The standard safe answ
er will be that raid 10 will give you better performance.
But if you are running a database on a EMC clarion SAN, it is possible that
you will never overflow the cache, or that the writes across 168 drives of r
aid 5 will provide excellent performance.|||Ken,
Yes it is EMC but not that many drivers. It was raid 5 and 0 already mapped
by Dell (builtin that way). I know about raid 10 but my real question is wha
t would be the best raid for clustered and noneclustered index files?
Thank you,
ktf

Q on Clustered and Nonclustered Index files to be on Raid

I have SAN mapped as Raid 5 and Raid 0. I have to place Clustered Index file (contains all clustered indexes) and Nonclustered Index file file (contains all nonclustered indexes). Which Raid level is the best and recommended for clustered and none cluster
ed index files to have a better performance? It is sql 2k.
Thank you,
Ktf
I would suggest that this is not enough information. The standard safe answer will be that raid 10 will give you better performance.
But if you are running a database on a EMC clarion SAN, it is possible that you will never overflow the cache, or that the writes across 168 drives of raid 5 will provide excellent performance.
|||Ken,
Yes it is EMC but not that many drivers. It was raid 5 and 0 already mapped by Dell (builtin that way). I know about raid 10 but my real question is what would be the best raid for clustered and noneclustered index files?
Thank you,
ktf

Q any group or freeze function

Hello,
I have a rectangle and a few textbox in it, now when I run report, the
textbox goes out of rectangle, Is there any way to group and freeze?Jim,
It sounds like the text box isn't actually IN the rectangle.
Select the textbox and cut it with Ctrl-X
Select the rectangle then paste with Ctrl-V
The textbox should now be inside the rectangle, try dragging it around,
it should be bound by the rectangle now.
Chris
JIM.H. wrote:
> Hello,
> I have a rectangle and a few textbox in it, now when I run report,
> the textbox goes out of rectangle, Is there any way to group and
> freeze?

Q : Does MSDE have an evaluation Period

Hi All,
Questions : I would like to know if MSDE has an evaluation period ? How long is the evaluation period ?
Thanking you in advance
Vikash
On Thu, 6 May 2004 01:36:04 -0700, vikash m tulsi wrote:

>Hi All,
>Questions : I would like to know if MSDE has an evaluation period ? How long is the evaluation period ?
>Thanking you in advance
>Vikash
Hi Vikash,
Since MSDE is free, you can use it as long as you like.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Q - Adding a database file

I am looking to add datafiles to a somewhat large db (40gb). If I add the
file - how does it get populated? Or does SQL Server start adding data from
that point forward to the file without balancing it?Data is added to new files going forward. SQL does not rebalance data
within data files.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"frankm" <frankm@.nospam.postalias> wrote in message
news:usm87CAoEHA.3520@.TK2MSFTNGP11.phx.gbl...
> I am looking to add datafiles to a somewhat large db (40gb). If I add the
> file - how does it get populated? Or does SQL Server start adding data
from
> that point forward to the file without balancing it?
>|||Thanks ... that helps
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:OqkaWMAoEHA.132@.TK2MSFTNGP14.phx.gbl...
> Data is added to new files going forward. SQL does not rebalance data
> within data files.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "frankm" <frankm@.nospam.postalias> wrote in message
> news:usm87CAoEHA.3520@.TK2MSFTNGP11.phx.gbl...
> > I am looking to add datafiles to a somewhat large db (40gb). If I add
the
> > file - how does it get populated? Or does SQL Server start adding data
> from
> > that point forward to the file without balancing it?
> >
> >
>|||One more thing -
Are there any problems with doing an "add file" to a publisher or subscriber
database?
"frankm" <frankm@.nospam.postalias> wrote in message
news:emSYVPAoEHA.2684@.TK2MSFTNGP11.phx.gbl...
> Thanks ... that helps
>
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:OqkaWMAoEHA.132@.TK2MSFTNGP14.phx.gbl...
> > Data is added to new files going forward. SQL does not rebalance data
> > within data files.
> >
> > --
> > Geoff N. Hiten
> > Microsoft SQL Server MVP
> > Senior Database Administrator
> > Careerbuilder.com
> >
> > I support the Professional Association for SQL Server
> > www.sqlpass.org
> >
> > "frankm" <frankm@.nospam.postalias> wrote in message
> > news:usm87CAoEHA.3520@.TK2MSFTNGP11.phx.gbl...
> > > I am looking to add datafiles to a somewhat large db (40gb). If I add
> the
> > > file - how does it get populated? Or does SQL Server start adding data
> > from
> > > that point forward to the file without balancing it?
> > >
> > >
> >
> >
>|||No problems. Replication abstracts everything at the database\filegroup
level. It doesn't care whether a filegroup has 1 or 10 underlying files.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"frankm" <frankm@.nospam.postalias> wrote in message
news:ehYPgkKoEHA.3712@.TK2MSFTNGP15.phx.gbl...
> One more thing -
> Are there any problems with doing an "add file" to a publisher or
subscriber
> database?
>
> "frankm" <frankm@.nospam.postalias> wrote in message
> news:emSYVPAoEHA.2684@.TK2MSFTNGP11.phx.gbl...
> > Thanks ... that helps
> >
> >
> >
> > "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> > news:OqkaWMAoEHA.132@.TK2MSFTNGP14.phx.gbl...
> > > Data is added to new files going forward. SQL does not rebalance data
> > > within data files.
> > >
> > > --
> > > Geoff N. Hiten
> > > Microsoft SQL Server MVP
> > > Senior Database Administrator
> > > Careerbuilder.com
> > >
> > > I support the Professional Association for SQL Server
> > > www.sqlpass.org
> > >
> > > "frankm" <frankm@.nospam.postalias> wrote in message
> > > news:usm87CAoEHA.3520@.TK2MSFTNGP11.phx.gbl...
> > > > I am looking to add datafiles to a somewhat large db (40gb). If I
add
> > the
> > > > file - how does it get populated? Or does SQL Server start adding
data
> > > from
> > > > that point forward to the file without balancing it?
> > > >
> > > >
> > >
> > >
> >
> >
>|||Thank you sir...
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:eu650yKoEHA.1300@.TK2MSFTNGP12.phx.gbl...
> No problems. Replication abstracts everything at the database\filegroup
> level. It doesn't care whether a filegroup has 1 or 10 underlying files.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "frankm" <frankm@.nospam.postalias> wrote in message
> news:ehYPgkKoEHA.3712@.TK2MSFTNGP15.phx.gbl...
> > One more thing -
> > Are there any problems with doing an "add file" to a publisher or
> subscriber
> > database?
> >
> >
> > "frankm" <frankm@.nospam.postalias> wrote in message
> > news:emSYVPAoEHA.2684@.TK2MSFTNGP11.phx.gbl...
> > > Thanks ... that helps
> > >
> > >
> > >
> > > "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> > > news:OqkaWMAoEHA.132@.TK2MSFTNGP14.phx.gbl...
> > > > Data is added to new files going forward. SQL does not rebalance
data
> > > > within data files.
> > > >
> > > > --
> > > > Geoff N. Hiten
> > > > Microsoft SQL Server MVP
> > > > Senior Database Administrator
> > > > Careerbuilder.com
> > > >
> > > > I support the Professional Association for SQL Server
> > > > www.sqlpass.org
> > > >
> > > > "frankm" <frankm@.nospam.postalias> wrote in message
> > > > news:usm87CAoEHA.3520@.TK2MSFTNGP11.phx.gbl...
> > > > > I am looking to add datafiles to a somewhat large db (40gb). If I
> add
> > > the
> > > > > file - how does it get populated? Or does SQL Server start adding
> data
> > > > from
> > > > > that point forward to the file without balancing it?
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>sql