Performance and tuning in SQL Server 2000

I have 2 table around 60 million records and grow substantially. The table i
s
created with non-clustered index (int datatype). Jobs are scheduled and
running in the background. Is there any way to see in the profiler what
queries are being run. I could not see the queries as the jobs are running.
How do i need to optimize the query in case i get the query.If you are using SQL Server 2005, you can save a .trc file (started before
the job rns and ending afterward) and give it to the database engine tuning
advisor.
In SQL Server 2000, you can do something similar with the index tuning
wizard.
A
"Sathya" <Sathya@.discussions.microsoft.com> wrote in message
news:558C10E8-7555-475C-852D-BF71685618F4@.microsoft.com...
>I have 2 table around 60 million records and grow substantially. The table
>is
> created with non-clustered index (int datatype). Jobs are scheduled and
> running in the background. Is there any way to see in the profiler what
> queries are being run. I could not see the queries as the jobs are
> running.
> How do i need to optimize the query in case i get the query.

Performance : Queries taking very long time (Urgent)

The production_table has ~20 million records and has identity key which is
clustered and has non clustered index on Created_Date.
The following queries taking very long time as the plan using index scan.
Please advise for optimization.
select *
from production_table p -- production_table has ~10 million records
where datediff(dd, p.Created_Date, getdate()) = 7
select *
from production_table p -- -- production_table has ~10 million records
where datediff(mm, p.created_date, getdate()) = 1
Thanks in advance.
Hmm.. How many rows do the queries return ? Theres lots one could advice,
such as do you really need a select * or one could state, creating a
clustered index on Created_Date column or even trying to rewrite the where
clause something like
where created_date = getdate-7 and so on..
I think thats related to SARG and how the optimiser would pick the right
plan.. But with so many records, and with the select * in there, even if
does do an index seek , it would follow with a bookmark lookup to get the
rest of the data which could be expensive as well..
So without knowing a whole lot about ur application, it would be wrong for
me to really give the right solution...
Probably someone else can chime in with something better.
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:%23$XOvrMDFHA.392@.TK2MSFTNGP14.phx.gbl...
> The production_table has ~20 million records and has identity key which
is
> clustered and has non clustered index on Created_Date.
> The following queries taking very long time as the plan using index scan.
> Please advise for optimization.
> select *
> from production_table p -- production_table has ~10 million records
> where datediff(dd, p.Created_Date, getdate()) = 7
>
> select *
> from production_table p -- -- production_table has ~10 million
records
> where datediff(mm, p.created_date, getdate()) = 1
>
> Thanks in advance.
>
|||I would try running UPDATE STATISTICS initially, then try an index hint and
then consider clustering the date column rather than the identity column. You
could also test the use of calculated columns with indexes on the calculated
columns IE: day_number and month_number.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"DKRReddy" wrote:

> The production_table has ~20 million records and has identity key which is
> clustered and has non clustered index on Created_Date.
> The following queries taking very long time as the plan using index scan.
> Please advise for optimization.
> select *
> from production_table p -- production_table has ~10 million records
> where datediff(dd, p.Created_Date, getdate()) = 7
>
> select *
> from production_table p -- -- production_table has ~10 million records
> where datediff(mm, p.created_date, getdate()) = 1
>
> Thanks in advance.
>
>
|||Calculate the fixed date range before you query. Always list the
columns you require rather than use SELECT *.
SELECT col1, col2, ...
FROM production_table
WHERE created_date >= '20050131'
AND created_date < '20050201'
SELECT col1, col2, ...
FROM production_table
WHERE created_date >= '20050206'
AND created_date < '20050207'
David Portas
SQL Server MVP
|||David is correct,... when you use a function in a where clause, the
optimizer is unable to use index statistics to determine the best index.
Pre-determining the date ranges as David suggests will give the optimizer
visibility into the index statistics... Performance will then depend on the
number of rows that satisfy the where clause...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:%23$XOvrMDFHA.392@.TK2MSFTNGP14.phx.gbl...
> The production_table has ~20 million records and has identity key which
> is
> clustered and has non clustered index on Created_Date.
> The following queries taking very long time as the plan using index scan.
> Please advise for optimization.
> select *
> from production_table p -- production_table has ~10 million records
> where datediff(dd, p.Created_Date, getdate()) = 7
>
> select *
> from production_table p -- -- production_table has ~10 million
> records
> where datediff(mm, p.created_date, getdate()) = 1
>
> Thanks in advance.
>

Performance : Queries taking very long time (Urgent)

The production_table has ~20 million records and has identity key which is
clustered and has non clustered index on Created_Date.
The following queries taking very long time as the plan using index scan.
Please advise for optimization.
select *
from production_table p -- production_table has ~10 million records
where datediff(dd, p.Created_Date, getdate()) = 7
select *
from production_table p -- -- production_table has ~10 million records
where datediff(mm, p.created_date, getdate()) = 1
Thanks in advance.Hmm.. How many rows do the queries return ? Theres lots one could advice,
such as do you really need a select * or one could state, creating a
clustered index on Created_Date column or even trying to rewrite the where
clause something like
where created_date = getdate-7 and so on..
I think thats related to SARG and how the optimiser would pick the right
plan.. But with so many records, and with the select * in there, even if
does do an index seek , it would follow with a bookmark lookup to get the
rest of the data which could be expensive as well..
So without knowing a whole lot about ur application, it would be wrong for
me to really give the right solution...
Probably someone else can chime in with something better.
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:%23$XOvrMDFHA.392@.TK2MSFTNGP14.phx.gbl...
> The production_table has ~20 million records and has identity key which
is
> clustered and has non clustered index on Created_Date.
> The following queries taking very long time as the plan using index scan.
> Please advise for optimization.
> select *
> from production_table p -- production_table has ~10 million records
> where datediff(dd, p.Created_Date, getdate()) = 7
>
> select *
> from production_table p -- -- production_table has ~10 million
records
> where datediff(mm, p.created_date, getdate()) = 1
>
> Thanks in advance.
>|||I would try running UPDATE STATISTICS initially, then try an index hint and
then consider clustering the date column rather than the identity column. You
could also test the use of calculated columns with indexes on the calculated
columns IE: day_number and month_number.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"DKRReddy" wrote:
> The production_table has ~20 million records and has identity key which is
> clustered and has non clustered index on Created_Date.
> The following queries taking very long time as the plan using index scan.
> Please advise for optimization.
> select *
> from production_table p -- production_table has ~10 million records
> where datediff(dd, p.Created_Date, getdate()) = 7
>
> select *
> from production_table p -- -- production_table has ~10 million records
> where datediff(mm, p.created_date, getdate()) = 1
>
> Thanks in advance.
>
>|||Calculate the fixed date range before you query. Always list the
columns you require rather than use SELECT *.
SELECT col1, col2, ...
FROM production_table
WHERE created_date >= '20050131'
AND created_date < '20050201'
SELECT col1, col2, ...
FROM production_table
WHERE created_date >= '20050206'
AND created_date < '20050207'
--
David Portas
SQL Server MVP
--|||David is correct,... when you use a function in a where clause, the
optimizer is unable to use index statistics to determine the best index.
Pre-determining the date ranges as David suggests will give the optimizer
visibility into the index statistics... Performance will then depend on the
number of rows that satisfy the where clause...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:%23$XOvrMDFHA.392@.TK2MSFTNGP14.phx.gbl...
> The production_table has ~20 million records and has identity key which
> is
> clustered and has non clustered index on Created_Date.
> The following queries taking very long time as the plan using index scan.
> Please advise for optimization.
> select *
> from production_table p -- production_table has ~10 million records
> where datediff(dd, p.Created_Date, getdate()) = 7
>
> select *
> from production_table p -- -- production_table has ~10 million
> records
> where datediff(mm, p.created_date, getdate()) = 1
>
> Thanks in advance.
>

Performance : Queries taking very long time (Urgent)

The production_table has ~20 million records and has identity key which is
clustered and has non clustered index on Created_Date.
The following queries taking very long time as the plan using index scan.
Please advise for optimization.
select *
from production_table p -- production_table has ~10 million records
where datediff(dd, p.Created_Date, getdate()) = 7
select *
from production_table p -- -- production_table has ~10 million records
where datediff(mm, p.created_date, getdate()) = 1
Thanks in advance.Hmm.. How many rows do the queries return ? Theres lots one could advice,
such as do you really need a select * or one could state, creating a
clustered index on Created_Date column or even trying to rewrite the where
clause something like
where created_date = getdate-7 and so on..
I think thats related to SARG and how the optimiser would pick the right
plan.. But with so many records, and with the select * in there, even if
does do an index seek , it would follow with a bookmark lookup to get the
rest of the data which could be expensive as well..
So without knowing a whole lot about ur application, it would be wrong for
me to really give the right solution...
Probably someone else can chime in with something better.
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:%23$XOvrMDFHA.392@.TK2MSFTNGP14.phx.gbl...
> The production_table has ~20 million records and has identity key which
is
> clustered and has non clustered index on Created_Date.
> The following queries taking very long time as the plan using index scan.
> Please advise for optimization.
> select *
> from production_table p -- production_table has ~10 million records
> where datediff(dd, p.Created_Date, getdate()) = 7
>
> select *
> from production_table p -- -- production_table has ~10 million
records
> where datediff(mm, p.created_date, getdate()) = 1
>
> Thanks in advance.
>|||I would try running UPDATE STATISTICS initially, then try an index hint and
then consider clustering the date column rather than the identity column. Yo
u
could also test the use of calculated columns with indexes on the calculated
columns IE: day_number and month_number.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"DKRReddy" wrote:

> The production_table has ~20 million records and has identity key which
is
> clustered and has non clustered index on Created_Date.
> The following queries taking very long time as the plan using index scan.
> Please advise for optimization.
> select *
> from production_table p -- production_table has ~10 million records
> where datediff(dd, p.Created_Date, getdate()) = 7
>
> select *
> from production_table p -- -- production_table has ~10 million record
s
> where datediff(mm, p.created_date, getdate()) = 1
>
> Thanks in advance.
>
>|||Calculate the fixed date range before you query. Always list the
columns you require rather than use SELECT *.
SELECT col1, col2, ...
FROM production_table
WHERE created_date >= '20050131'
AND created_date < '20050201'
SELECT col1, col2, ...
FROM production_table
WHERE created_date >= '20050206'
AND created_date < '20050207'
David Portas
SQL Server MVP
--|||David is correct,... when you use a function in a where clause, the
optimizer is unable to use index statistics to determine the best index.
Pre-determining the date ranges as David suggests will give the optimizer
visibility into the index statistics... Performance will then depend on the
number of rows that satisfy the where clause...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:%23$XOvrMDFHA.392@.TK2MSFTNGP14.phx.gbl...
> The production_table has ~20 million records and has identity key which
> is
> clustered and has non clustered index on Created_Date.
> The following queries taking very long time as the plan using index scan.
> Please advise for optimization.
> select *
> from production_table p -- production_table has ~10 million records
> where datediff(dd, p.Created_Date, getdate()) = 7
>
> select *
> from production_table p -- -- production_table has ~10 million
> records
> where datediff(mm, p.created_date, getdate()) = 1
>
> Thanks in advance.
>

Performance

New PE2800 3.2gb Xeon, 2gb ram, sql 2005 workgroup ed.

I have a db that has been imported from access. One of the tables has a 1.8 million records. It takes a couple of minutes to pull this data up from the sql manager whereas in access it only took a couple of seconds. Other tables take longer to pull-up in sql as well. If I use access to connect to the sql server and and pull up this long table, it doesnt take as long as the sql manager but it still takes a lot longer than the access db does to pull up the same info.

Why does this very basic query take so long?

It also utlizes 50-70% of processor time while it trying to return these records.

I cant seem to find a good explaination for this behavior?

Thanks,

How is the table indexed in SQL Server? Is it the same as in Access?

performance

hi all
how can I increase the speed of an SQL Server damaticly ?
configuration:
W2k, SQL Server 2000, 2 processors (1.4GHz)
table:
1000000 records
queryresult:
450000 records
time:
11s
i tried to set indexes on fields where i'm search for. but
there was not a lot of speed available. for my oppinion
it's a very poor value for a sql server to find 450000
records!!!
any hints to optimize the query or the server ?
thankx a lot
tomWhere do you issue that query from? You still have to move those 450,000
rows from the server to your client and that is going to take some time.
Even on my local machine, if I do SELECT * FROM table, where that table has
300,000 rows and a lot of columns it takes 40 seconds for the results to
show in Query Analyzer, but SELECT COUNT(*) is sub second, even when I make
sure that it uses a table scan by including a where clause on a non indexed
column that returns all rows.
--
Jacco Schalkwijk
SQL Server MVP
"thomas.marti@.netrics.ch" <anonymous@.discussions.microsoft.com> wrote in
message news:081f01c3c0a1$2df367a0$a401280a@.phx.gbl...
> hi all
> how can I increase the speed of an SQL Server damaticly ?
> configuration:
> W2k, SQL Server 2000, 2 processors (1.4GHz)
> table:
> 1000000 records
> queryresult:
> 450000 records
> time:
> 11s
> i tried to set indexes on fields where i'm search for. but
> there was not a lot of speed available. for my oppinion
> it's a very poor value for a sql server to find 450000
> records!!!
> any hints to optimize the query or the server ?
> thankx a lot
> tom|||it's on the same machine. so i think that it cannot be so
slow isnt't it?
but how do other people work with lots of records? one
million records is not really a big amount for a
database!!!
>--Original Message--
>Where do you issue that query from? You still have to
move those 450,000
>rows from the server to your client and that is going to
take some time.
>Even on my local machine, if I do SELECT * FROM table,
where that table has
>300,000 rows and a lot of columns it takes 40 seconds for
the results to
>show in Query Analyzer, but SELECT COUNT(*) is sub
second, even when I make
>sure that it uses a table scan by including a where
clause on a non indexed
>column that returns all rows.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"thomas.marti@.netrics.ch"
<anonymous@.discussions.microsoft.com> wrote in
>message news:081f01c3c0a1$2df367a0$a401280a@.phx.gbl...
>> hi all
>> how can I increase the speed of an SQL Server
damaticly ?
>> configuration:
>> W2k, SQL Server 2000, 2 processors (1.4GHz)
>> table:
>> 1000000 records
>> queryresult:
>> 450000 records
>> time:
>> 11s
>> i tried to set indexes on fields where i'm search for.
but
>> there was not a lot of speed available. for my oppinion
>> it's a very poor value for a sql server to find 450000
>> records!!!
>> any hints to optimize the query or the server ?
>> thankx a lot
>> tom
>
>.
>|||One million rows is not a lot for a database, but one million (or 450,000)
rows is a whole lot to return to your user. They are not going to look
through 450,000 rows just to find something.
As I said before the bottleneck is with getting the results to the client,
not with getting the rows from the server.
--
Jacco Schalkwijk
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:093b01c3c0b9$50a63b20$a401280a@.phx.gbl...
> it's on the same machine. so i think that it cannot be so
> slow isnt't it?
> but how do other people work with lots of records? one
> million records is not really a big amount for a
> database!!!
> >--Original Message--
> >Where do you issue that query from? You still have to
> move those 450,000
> >rows from the server to your client and that is going to
> take some time.
> >
> >Even on my local machine, if I do SELECT * FROM table,
> where that table has
> >300,000 rows and a lot of columns it takes 40 seconds for
> the results to
> >show in Query Analyzer, but SELECT COUNT(*) is sub
> second, even when I make
> >sure that it uses a table scan by including a where
> clause on a non indexed
> >column that returns all rows.
> >
> >--
> >Jacco Schalkwijk
> >SQL Server MVP
> >
> >
> >"thomas.marti@.netrics.ch"
> <anonymous@.discussions.microsoft.com> wrote in
> >message news:081f01c3c0a1$2df367a0$a401280a@.phx.gbl...
> >> hi all
> >> how can I increase the speed of an SQL Server
> damaticly ?
> >>
> >> configuration:
> >> W2k, SQL Server 2000, 2 processors (1.4GHz)
> >>
> >> table:
> >> 1000000 records
> >>
> >> queryresult:
> >> 450000 records
> >>
> >> time:
> >> 11s
> >>
> >> i tried to set indexes on fields where i'm search for.
> but
> >> there was not a lot of speed available. for my oppinion
> >> it's a very poor value for a sql server to find 450000
> >> records!!!
> >> any hints to optimize the query or the server ?
> >>
> >> thankx a lot
> >> tom
> >
> >
> >.
> >

Performace Monitor and SQL Profiler

I see, I will have to check that out on if it has any affect on user database
or not. But today I did a trace that logged about 250,000 records on
another database, and that server didn't notice any deadlocks or lock
timeouts. But I did take your advice and this time ran the SQL Monitor on a
different computer. So maybe that had some affect.
So I do suspect me running it on the serer caused locks but might have
caused some slow down. So I think the application is either timing out too
quickly or something else is a miss. Thank-you for your reply.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Ekrem ?nsoy" wrote:

> Well... I'm not sure if SQL Profiler may cause locks or not however
> Microsoft recommends using these kind of tools (SQL Profiler, Performance
> Monitor) from another machine. Because they effect performance on the
> machine they work. However, in this case they also recommend using a
> dedicated connection between those two machines to avoid network traffic
> because of using these tools if possible.
> So, in your case, yes, they have affect on the SQL Server somehow.
> --
> Ekrem ?nsoy
>
> "Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
> news:E838BE3D-DDAF-46C3-A9B0-7F102C59D806@.microsoft.com...
>
Hi
SQL Profiler will not create database locks unless you are logging to a
database table, which is not usually recommended. In whioch case it is
unlikely that other applications are using that table (or database!)
If you can't log from a separate machine on a dedicated lan, you can use a
server side trace. Make sure that any trace files are not using the same
discs as SQL Server preferrably have fast spindles for it's exclusive use
(although this is rarely possible!)
If you use the SQL Profiler GUI it will use space on the TEMP directory
(even if logging to a file) make sure that this is not the system disc as
running out of space on that, will cause the system to hang.
John
"Mohit K. Gupta" wrote:
[vbcol=seagreen]
> I see, I will have to check that out on if it has any affect on user database
> or not. But today I did a trace that logged about 250,000 records on
> another database, and that server didn't notice any deadlocks or lock
> timeouts. But I did take your advice and this time ran the SQL Monitor on a
> different computer. So maybe that had some affect.
> So I do suspect me running it on the serer caused locks but might have
> caused some slow down. So I think the application is either timing out too
> quickly or something else is a miss. Thank-you for your reply.
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "Ekrem ?nsoy" wrote:
|||Thanks Guys ;-).
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> SQL Profiler will not create database locks unless you are logging to a
> database table, which is not usually recommended. In whioch case it is
> unlikely that other applications are using that table (or database!)
> If you can't log from a separate machine on a dedicated lan, you can use a
> server side trace. Make sure that any trace files are not using the same
> discs as SQL Server preferrably have fast spindles for it's exclusive use
> (although this is rarely possible!)
> If you use the SQL Profiler GUI it will use space on the TEMP directory
> (even if logging to a file) make sure that this is not the system disc as
> running out of space on that, will cause the system to hang.
> John
> "Mohit K. Gupta" wrote:

Percentile Calculation

Hi All,

I have around 1000 employee records containing employee number and their salary. How do i calculate percentile on these records?

For example
How to calculate 25th Percentile on Salary of these 1000 records?

Hope I am clear with my question.

Thanks in advance.

Regards,
qALook up the "TOP n [PERCENT]" clause in Books Online.

select top 25 percent * from [YourTable]|||Hi,
Thanks for your immediate response. But you got it wrong. Its not Percentage, its PERCENTILE. If you have used MS-Excel there is a function called
PERCENTILE(Array, k)

For example if I have 10,20,30,40,50 as values
then 30th PERCENTILE of this comes as 22

Thanks
qA|||No, I don't get how the calculation is done. And Excel's Books Online (which is normally very good at describing statistical functions) gives no explanation of the algorithm at all.|||Wikipedia, while citing Excel's function as an example, appears to me to be describing behavior similiar to TOP %:

http://en.wikipedia.org/wiki/Percentile|||Hi,
The top clause in a query will return one ore more than one records. But Percentile will always return one and only one value.|||Yes, but how to calculate that value? I found this lovely quote:

"There is no universally accepted definition of a percentile."

on this website: http://cnx.rice.edu/content/m10805/latest/

Very curious. I guess what intrigues me is that I have been using Excel and doing applied business statistics and data mining for more than a decade and have not run into this, or ever used that Excel function before.

Try describing what you want to do, without referring to the Excel function.|||OK, the more I research PERCENTILE calculation, the more I understand why I've never used it. It is about the stupidest statistical measure I have ever seen. Why? Because makes a strict linear interpolation between just two data points out of the set, without any regard to the distribution of the data.

As an example, the 30th percentile of your values (10, 20, 30, 40, 50) is 22, as Excel calculates it. But the 30th percentile of the following values (0, 10, 20, 1000, 1000000) is...guess what...also 22.

I see no practical purpose for such a measure unless your goal is to mislead your audience.