Performance Tuning

I am a SQL DBA newbie. I like to tune a database that I
know very little about. Other than creating indexes on
the tables that are frequently used, and rebuilding them
occasionally, can you list the different tuning tasks I
need to do please. Thank you.
Dianemonitor your slowest sprocs and tune them individually. There is a template
in SQL Profiler that will catch sproc duration statistics for you.
check peformance counters (IO, RAM, CPU, Network) for bottlenecks
dont know what to fix if you dont know what is broken.
Greg Jackson
PDX, Oregon|||Thank you.
>--Original Message--
>monitor your slowest sprocs and tune them individually.
There is a template
>in SQL Profiler that will catch sproc duration statistics
for you.
>
>check peformance counters (IO, RAM, CPU, Network) for
bottlenecks
>dont know what to fix if you dont know what is broken.
>
>Greg Jackson
>PDX, Oregon
>
>.
>|||Here are some links that may help:
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/operate/opsguide/default.asp
http://www.microsoft.com/sql/techinfo/administration/2000/perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly
SQL Server MVP
"Diane Siona" <anonymous@.discussions.microsoft.com> wrote in message
news:abeb01c3ec26$6f7ab150$a301280a@.phx.gbl...
> I am a SQL DBA newbie. I like to tune a database that I
> know very little about. Other than creating indexes on
> the tables that are frequently used, and rebuilding them
> occasionally, can you list the different tuning tasks I
> need to do please. Thank you.
> Diane

Performance Tuning

I have a query that i'm trying to performance tune a little better.
i'm stuck on one thing. i have a table that i join on mulitple times
that selects the max date for a particular status per id.
the table looks as follows:
CREATE TABLE [dbo].[TABLE_STATUS] (
[TableStatusID] [int] NOT NULL ,
[TableID] [int] NOT NULL ,
[StatusTypeID] [int] NOT NULL ,
[StatusDate] [datetime] NOT NULL ,
[CreateDate] [datetime] NOT NULL ,
[StageTypeID] [int] NULL
) ON [PRIMARY]
GO
The query i'm using looks something like this:
SELECT field1, field2, field3
(SELECT TOP 1 StatusDate
FROM dbo.TABLE_STATUS LS
WHERE LS.TableID= SR.TableIDAND StatusTypeID = 'xyx'
ORDER BY StatusDate DESC) AS someDate,
(SELECT TOP 1 StatusDate
FROM dbo.TABLE_STATUS LS
WHERE LS.TableID= SR.TableIDAND StatusTypeID = 'xxy'
ORDER BY StatusDate DESC) AS someDate,
(SELECT TOP 1 StatusDate
FROM dbo.TABLE_STATUS LS
WHERE LS.TableID= SR.TableIDAND StatusTypeID = 'yxx'
ORDER BY StatusDate DESC) AS someDate
FROM dbo.someTable SR
WHERE etc.
I was wondering if there is a better to way to select the max date for
each loan by date desc. some queries use up too 25 different
max(dates) per select statement. There just has to be a better way
performance wise.
Thanks ahead of time.On 17 May 2005 11:01:01 -0700, GlennThomas5 wrote:

>I have a query that i'm trying to performance tune a little better.
>i'm stuck on one thing. i have a table that i join on mulitple times
>that selects the max date for a particular status per id.
>the table looks as follows:
>CREATE TABLE [dbo].[TABLE_STATUS] (
> [TableStatusID] [int] NOT NULL ,
> [TableID] [int] NOT NULL ,
> [StatusTypeID] [int] NOT NULL ,
> [StatusDate] [datetime] NOT NULL ,
> [CreateDate] [datetime] NOT NULL ,
> [StageTypeID] [int] NULL
> ) ON [PRIMARY]
>GO
>The query i'm using looks something like this:
>SELECT field1, field2, field3
> (SELECT TOP 1 StatusDate
> FROM dbo.TABLE_STATUS LS
> WHERE LS.TableID= SR.TableIDAND StatusTypeID = 'xyx'
> ORDER BY StatusDate DESC) AS someDate,
> (SELECT TOP 1 StatusDate
> FROM dbo.TABLE_STATUS LS
> WHERE LS.TableID= SR.TableIDAND StatusTypeID = 'xxy'
> ORDER BY StatusDate DESC) AS someDate,
> (SELECT TOP 1 StatusDate
> FROM dbo.TABLE_STATUS LS
> WHERE LS.TableID= SR.TableIDAND StatusTypeID = 'yxx'
> ORDER BY StatusDate DESC) AS someDate
> FROM dbo.someTable SR
> WHERE etc.
>I was wondering if there is a better to way to select the max date for
>each loan by date desc. some queries use up too 25 different
>max(dates) per select statement. There just has to be a better way
>performance wise.
Hi Glenn,
Try if this works for you:
SELECT field1, field2, field3,
MAX(CASE WHEN LS.StatusTypeID = 'xyx' THEN LS.StatusDate END),
MAX(CASE WHEN LS.StatusTypeID = 'xxy' THEN LS.StatusDate END),
MAX(CASE WHEN LS.StatusTypeID = 'yxx' THEN LS.StatusDate END)
FROM dbo.someTable SR
JOIN dbo.TABLE_STATUS LS
ON LS.TableID= SR.TableID
WHERE ...
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks i'm going to check it out right now. =)|||well i check it out and that seems to be pulling the 1 max(date) across
all the typeid's. i need the max(date) for each typeid.|||tweaking a few more things. i think i might have it.|||this worked perfect. thanks again.

Performance Tuning

I am a SQL DBA newbie. I like to tune a database that I
know very little about. Other than creating indexes on
the tables that are frequently used, and rebuilding them
occasionally, can you list the different tuning tasks I
need to do please. Thank you.
Dianemonitor your slowest sprocs and tune them individually. There is a template
in SQL Profiler that will catch sproc duration statistics for you.
check peformance counters (IO, RAM, CPU, Network) for bottlenecks
dont know what to fix if you dont know what is broken.
Greg Jackson
PDX, Oregon|||Thank you.

>--Original Message--
>monitor your slowest sprocs and tune them individually.
There is a template
>in SQL Profiler that will catch sproc duration statistics
for you.
>
>check peformance counters (IO, RAM, CPU, Network) for
bottlenecks
>dont know what to fix if you dont know what is broken.
>
>Greg Jackson
>PDX, Oregon
>
>.
>|||Here are some links that may help:
http://www.microsoft.com/technet/tr...ide/default.asp
http://www.microsoft.com/sql/techin.../perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
Andrew J. Kelly
SQL Server MVP
"Diane Siona" <anonymous@.discussions.microsoft.com> wrote in message
news:abeb01c3ec26$6f7ab150$a301280a@.phx.gbl...
> I am a SQL DBA newbie. I like to tune a database that I
> know very little about. Other than creating indexes on
> the tables that are frequently used, and rebuilding them
> occasionally, can you list the different tuning tasks I
> need to do please. Thank you.
> Dianesql

Performance Tuning

I have a query that i'm trying to performance tune a little better.
i'm stuck on one thing. i have a table that i join on mulitple times
that selects the max date for a particular status per id.
the table looks as follows:
CREATE TABLE [dbo].[TABLE_STATUS] (
[TableStatusID] [int] NOT NULL ,
[TableID] [int] NOT NULL ,
[StatusTypeID] [int] NOT NULL ,
[StatusDate] [datetime] NOT NULL ,
[CreateDate] [datetime] NOT NULL ,
[StageTypeID] [int] NULL
) ON [PRIMARY]
GO
The query i'm using looks something like this:
SELECT field1, field2, field3
(SELECT TOP 1 StatusDate
FROM dbo.TABLE_STATUS LS
WHERE LS.TableID= SR.TableIDAND StatusTypeID = 'xyx'
ORDER BY StatusDate DESC) AS someDate,
(SELECT TOP 1 StatusDate
FROM dbo.TABLE_STATUS LS
WHERE LS.TableID= SR.TableIDAND StatusTypeID = 'xxy'
ORDER BY StatusDate DESC) AS someDate,
(SELECT TOP 1 StatusDate
FROM dbo.TABLE_STATUS LS
WHERE LS.TableID= SR.TableIDAND StatusTypeID = 'yxx'
ORDER BY StatusDate DESC) AS someDate
FROM dbo.someTable SR
WHERE etc.
I was wondering if there is a better to way to select the max date for
each loan by date desc. some queries use up too 25 different
max(dates) per select statement. There just has to be a better way
performance wise.
Thanks ahead of time.
On 17 May 2005 11:01:01 -0700, GlennThomas5 wrote:

>I have a query that i'm trying to performance tune a little better.
>i'm stuck on one thing. i have a table that i join on mulitple times
>that selects the max date for a particular status per id.
>the table looks as follows:
>CREATE TABLE [dbo].[TABLE_STATUS] (
>[TableStatusID] [int] NOT NULL ,
>[TableID] [int] NOT NULL ,
>[StatusTypeID] [int] NOT NULL ,
>[StatusDate] [datetime] NOT NULL ,
>[CreateDate] [datetime] NOT NULL ,
>[StageTypeID] [int] NULL
>) ON [PRIMARY]
>GO
>The query i'm using looks something like this:
>SELECT field1, field2, field3
> (SELECT TOP 1 StatusDate
> FROM dbo.TABLE_STATUS LS
> WHERE LS.TableID= SR.TableIDAND StatusTypeID = 'xyx'
> ORDER BY StatusDate DESC) AS someDate,
> (SELECT TOP 1 StatusDate
> FROM dbo.TABLE_STATUS LS
> WHERE LS.TableID= SR.TableIDAND StatusTypeID = 'xxy'
> ORDER BY StatusDate DESC) AS someDate,
> (SELECT TOP 1 StatusDate
> FROM dbo.TABLE_STATUS LS
> WHERE LS.TableID= SR.TableIDAND StatusTypeID = 'yxx'
> ORDER BY StatusDate DESC) AS someDate
> FROM dbo.someTable SR
> WHERE etc.
>I was wondering if there is a better to way to select the max date for
>each loan by date desc. some queries use up too 25 different
>max(dates) per select statement. There just has to be a better way
>performance wise.
Hi Glenn,
Try if this works for you:
SELECT field1, field2, field3,
MAX(CASE WHEN LS.StatusTypeID = 'xyx' THEN LS.StatusDate END),
MAX(CASE WHEN LS.StatusTypeID = 'xxy' THEN LS.StatusDate END),
MAX(CASE WHEN LS.StatusTypeID = 'yxx' THEN LS.StatusDate END)
FROM dbo.someTable SR
JOIN dbo.TABLE_STATUS LS
ON LS.TableID= SR.TableID
WHERE ...
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks i'm going to check it out right now. =)
|||well i check it out and that seems to be pulling the 1 max(date) across
all the typeid's. i need the max(date) for each typeid.
|||tweaking a few more things. i think i might have it.
|||this worked perfect. thanks again.

Performance Tuning

I have a query that i'm trying to performance tune a little better.
i'm stuck on one thing. i have a table that i join on mulitple times
that selects the max date for a particular status per id.
the table looks as follows:
CREATE TABLE [dbo].[TABLE_STATUS] (
[TableStatusID] [int] NOT NULL ,
[TableID] [int] NOT NULL ,
[StatusTypeID] [int] NOT NULL ,
[StatusDate] [datetime] NOT NULL ,
[CreateDate] [datetime] NOT NULL ,
[StageTypeID] [int] NULL
) ON [PRIMARY]
GO
The query i'm using looks something like this:
SELECT field1, field2, field3
(SELECT TOP 1 StatusDate
FROM dbo.TABLE_STATUS LS
WHERE LS.TableID= SR.TableIDAND StatusTypeID = 'xyx'
ORDER BY StatusDate DESC) AS someDate,
(SELECT TOP 1 StatusDate
FROM dbo.TABLE_STATUS LS
WHERE LS.TableID= SR.TableIDAND StatusTypeID = 'xxy'
ORDER BY StatusDate DESC) AS someDate,
(SELECT TOP 1 StatusDate
FROM dbo.TABLE_STATUS LS
WHERE LS.TableID= SR.TableIDAND StatusTypeID = 'yxx'
ORDER BY StatusDate DESC) AS someDate
FROM dbo.someTable SR
WHERE etc.
I was wondering if there is a better to way to select the max date for
each loan by date desc. some queries use up too 25 different
max(dates) per select statement. There just has to be a better way
performance wise.
Thanks ahead of time.On 17 May 2005 11:01:01 -0700, GlennThomas5 wrote:
>I have a query that i'm trying to performance tune a little better.
>i'm stuck on one thing. i have a table that i join on mulitple times
>that selects the max date for a particular status per id.
>the table looks as follows:
>CREATE TABLE [dbo].[TABLE_STATUS] (
> [TableStatusID] [int] NOT NULL ,
> [TableID] [int] NOT NULL ,
> [StatusTypeID] [int] NOT NULL ,
> [StatusDate] [datetime] NOT NULL ,
> [CreateDate] [datetime] NOT NULL ,
> [StageTypeID] [int] NULL
>) ON [PRIMARY]
>GO
>The query i'm using looks something like this:
>SELECT field1, field2, field3
> (SELECT TOP 1 StatusDate
> FROM dbo.TABLE_STATUS LS
> WHERE LS.TableID= SR.TableIDAND StatusTypeID = 'xyx'
> ORDER BY StatusDate DESC) AS someDate,
> (SELECT TOP 1 StatusDate
> FROM dbo.TABLE_STATUS LS
> WHERE LS.TableID= SR.TableIDAND StatusTypeID = 'xxy'
> ORDER BY StatusDate DESC) AS someDate,
> (SELECT TOP 1 StatusDate
> FROM dbo.TABLE_STATUS LS
> WHERE LS.TableID= SR.TableIDAND StatusTypeID = 'yxx'
> ORDER BY StatusDate DESC) AS someDate
> FROM dbo.someTable SR
> WHERE etc.
>I was wondering if there is a better to way to select the max date for
>each loan by date desc. some queries use up too 25 different
>max(dates) per select statement. There just has to be a better way
>performance wise.
Hi Glenn,
Try if this works for you:
SELECT field1, field2, field3,
MAX(CASE WHEN LS.StatusTypeID = 'xyx' THEN LS.StatusDate END),
MAX(CASE WHEN LS.StatusTypeID = 'xxy' THEN LS.StatusDate END),
MAX(CASE WHEN LS.StatusTypeID = 'yxx' THEN LS.StatusDate END)
FROM dbo.someTable SR
JOIN dbo.TABLE_STATUS LS
ON LS.TableID= SR.TableID
WHERE ...
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks i'm going to check it out right now. =)|||well i check it out and that seems to be pulling the 1 max(date) across
all the typeid's. i need the max(date) for each typeid.|||tweaking a few more things. i think i might have it.|||this worked perfect. thanks again.sql