Performance Problem

The query below is taking 3-4 seconds to run under a light load, which
seems to be a bit lengthy for the indexes that are in place and the
amount of data that exists in the tables. I have outlined everything
below, including all table definitions, indexes, and row counts. Any
help at all will be appreciated. It seems no matter how I think an
index will function it never seems to work properly.
==
BEGIN QUERY
==
SELECT tblC.catDesc AS Category_Name,
COUNT(DISTINCT tblS.set_ID) AS Set_Count,
tblC.cat_ID AS Category_ID,
COUNT(tblI.Img_ID) AS Image_Count,
MIN(tblI.Img_ID) AS Image_ID,
(
SELECT COUNT(tblI2.Img_ID)
FROM tblImage tblI2
LEFT JOIN tblSets tblS2 ON tblS2.set_ID = tblI2.set_ID
WHERE tblI2.d_t > @.d_t
AND tblI2.cat_ID = tblC.cat_ID
AND tblI2.display_status = 1
) AS New_Image_Count,
(
SELECT COUNT(DISTINCT tblI3.set_ID)
FROM tblImage tblI3
LEFT JOIN tblSets tblS3 ON tblS3.set_ID = tblI3.set_ID
WHERE tblI3.d_t > @.d_t
AND tblI3.cat_ID = tblC.cat_ID
) AS New_Set_Count
FROM tblCategories tblC
LEFT JOIN tblImage tblI ON tblC.cat_ID = tblI.cat_ID
LEFT JOIN tblSets tblS ON tblI.set_ID = tblS.set_ID
WHERE tblC.skin_ID = @.skin_ID
GROUP BY tblC.cat_id, tblC.catDesc
==
END QUERY
==
==
tblImage (approx. 71000 rows)
==
Definition:
Img_ID (int, Not Null) - PK
set_ID (int, Null)
cat_ID (int, Null)
d_t (datetime, Null)
display_status (int, Null)
Indexes:
1. Img_ID (clustered)
2. cat_id DESC, display_status DESC, d_t DESC
3. d_t DESC, display_status DESC, set_ID, cat_ID
4. set_ID DESC
==
END tblImage
==
==
tblCategories (approx. 35 rows)
==
Definition:
cat_ID (int, Not Null) - PK
catDesc (varchar(25), Null)
skin_ID (int, Null)
Indexes:
1. cat_ID (clustered)
2. skin_ID, cat_ID
==
END tblCategories
==
==
tblSets (approx. 1500 rows)
==
Definition:
set_ID (int, Not Null) - PK
setName (varchar(25), Null)
setKeywords (varchar(500), Null)
Indexes:
1. set_ID (clustered)
==
END tblSets
==If I understand the query correctly, (If set_ID is unique in tblSets) Then
the following might work and should be faster since it doesn't have the
subquerys...
Select C.catDesc Category_Name,
Count(Distinct S.set_ID) Set_Count,
C.cat_ID Category_ID,
Count(tblI.Img_ID) Image_Count,
Min(I.Img_ID) Image_ID,
Sum(Case When I.d_t = @.d_t
And display_status = 1
Then 1 End) New_Image_Count,
Sum(Case When I.d_t = @.d_t
Then 1 End) New_Set_Count
From tblCategories C
Left Join tblImage I
On I.cat_ID = C.cat_ID
Left Join tblSets S
On S.set_ID = I.set_ID
Where C.skin_ID = @.skin_ID
Group By C.cat_id, C.catDesc
"iTISTIC@.gmail.com" wrote:

> The query below is taking 3-4 seconds to run under a light load, which
> seems to be a bit lengthy for the indexes that are in place and the
> amount of data that exists in the tables. I have outlined everything
> below, including all table definitions, indexes, and row counts. Any
> help at all will be appreciated. It seems no matter how I think an
> index will function it never seems to work properly.
> ==
> BEGIN QUERY
> ==
> SELECT tblC.catDesc AS Category_Name,
> COUNT(DISTINCT tblS.set_ID) AS Set_Count,
> tblC.cat_ID AS Category_ID,
> COUNT(tblI.Img_ID) AS Image_Count,
> MIN(tblI.Img_ID) AS Image_ID,
> (
> SELECT COUNT(tblI2.Img_ID)
> FROM tblImage tblI2
> LEFT JOIN tblSets tblS2 ON tblS2.set_ID = tblI2.set_ID
> WHERE tblI2.d_t > @.d_t
> AND tblI2.cat_ID = tblC.cat_ID
> AND tblI2.display_status = 1
> ) AS New_Image_Count,
> (
> SELECT COUNT(DISTINCT tblI3.set_ID)
> FROM tblImage tblI3
> LEFT JOIN tblSets tblS3 ON tblS3.set_ID = tblI3.set_ID
> WHERE tblI3.d_t > @.d_t
> AND tblI3.cat_ID = tblC.cat_ID
> ) AS New_Set_Count
> FROM tblCategories tblC
> LEFT JOIN tblImage tblI ON tblC.cat_ID = tblI.cat_ID
> LEFT JOIN tblSets tblS ON tblI.set_ID = tblS.set_ID
> WHERE tblC.skin_ID = @.skin_ID
> GROUP BY tblC.cat_id, tblC.catDesc
> ==
> END QUERY
> ==
>
> ==
> tblImage (approx. 71000 rows)
> ==
> Definition:
> Img_ID (int, Not Null) - PK
> set_ID (int, Null)
> cat_ID (int, Null)
> d_t (datetime, Null)
> display_status (int, Null)
> Indexes:
> 1. Img_ID (clustered)
> 2. cat_id DESC, display_status DESC, d_t DESC
> 3. d_t DESC, display_status DESC, set_ID, cat_ID
> 4. set_ID DESC
> ==
> END tblImage
> ==
> ==
> tblCategories (approx. 35 rows)
> ==
> Definition:
> cat_ID (int, Not Null) - PK
> catDesc (varchar(25), Null)
> skin_ID (int, Null)
> Indexes:
> 1. cat_ID (clustered)
> 2. skin_ID, cat_ID
> ==
> END tblCategories
> ==
>
> ==
> tblSets (approx. 1500 rows)
> ==
> Definition:
> set_ID (int, Not Null) - PK
> setName (varchar(25), Null)
> setKeywords (varchar(500), Null)
> Indexes:
> 1. set_ID (clustered)
> ==
> END tblSets
> ==
>

performance problem

every 3-4 days I have to restart the sql service because some users are
complaining of poor performance in a specific form ( where they are
intensively add some small info but constantly with speed, and the process
of introduction is always behind where it should be ).
I made a test db where I restored the original db and I noticed that it's
working with normal speed in this parallel database.
My initial supposition was that it should work with the same low speed in
this new database, but it doesn't.
So I think this situation it's somewhat related with the database that it's
intensively worked with, and not with the memory of the server.
In 3-4 days the sql server process grows to about 500MB and if I let the
process for a week it goes to 800MB.
So to resume :
there is poor performance in 3-4 days from restart on a database that is
intensively worked on ,
but a normal performance on a duplicate database on the same server.
The server is a P4 at 2GHz with 1280MB of RAM and a scsi hard disk for
storing the databases.
Thanks for any clues
Fatboyslimro wrote:
> every 3-4 days I have to restart the sql service because some users
> are complaining of poor performance in a specific form ( where they
> are intensively add some small info but constantly with speed, and
> the process of introduction is always behind where it should be ).
> I made a test db where I restored the original db and I noticed that
> it's working with normal speed in this parallel database.
> My initial supposition was that it should work with the same low
> speed in this new database, but it doesn't.
> So I think this situation it's somewhat related with the database
> that it's intensively worked with, and not with the memory of the
> server.
> In 3-4 days the sql server process grows to about 500MB and if I let
> the process for a week it goes to 800MB.
>
> So to resume :
> there is poor performance in 3-4 days from restart on a database that
> is intensively worked on ,
> but a normal performance on a duplicate database on the same server.
>
> The server is a P4 at 2GHz with 1280MB of RAM and a scsi hard disk for
> storing the databases.
> Thanks for any clues
Memory issue sounds fine. SQL Server uses memory as needed up to its set
maximum or what's available in the OS and does not release memory
without a struggle. As more data is read into cache, the memory
footprint will increase. If you are running into a situation where the
OS needs more memory or you have other applications running on the same
server (lie IIS) and they are struggling, you need to set SQL Server to
only use a maximum amount of memory. You can do this easily from SQL
Enterprise Manager.
There could be other problems that are causing the slowness, however.
Report back to us whether you have other application running on the
server besides SQL Server.
David Gugick
Imceda Software
www.imceda.com
|||The server is also a file server and a DC.
But the thing is that in the same time that this database is running slow,
on a duplicate database on the same server the applications are running ok.
the sql server is limited to 800MB of RAM from a total of 1280MB
( before I made this duplicate database I thought that when it's going slow
on a database it will run slow on all the databases on the server - and I
wanted to do some maintenance operations on the duplicate db to see if this
is the problem, but if it runs slow on all the databases it isn't a problem
of indexing or something like this ).
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:e7H8kE64EHA.2964@.TK2MSFTNGP15.phx.gbl...
> Fatboyslimro wrote:
> Memory issue sounds fine. SQL Server uses memory as needed up to its set
> maximum or what's available in the OS and does not release memory
> without a struggle. As more data is read into cache, the memory
> footprint will increase. If you are running into a situation where the
> OS needs more memory or you have other applications running on the same
> server (lie IIS) and they are struggling, you need to set SQL Server to
> only use a maximum amount of memory. You can do this easily from SQL
> Enterprise Manager.
> There could be other problems that are causing the slowness, however.
> Report back to us whether you have other application running on the
> server besides SQL Server.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Fatboyslimro wrote:
> The server is also a file server and a DC.
> But the thing is that in the same time that this database is running
> slow, on a duplicate database on the same server the applications are
> running ok.
> the sql server is limited to 800MB of RAM from a total of 1280MB
> ( before I made this duplicate database I thought that when it's
> going slow on a database it will run slow on all the databases on the
> server - and I wanted to do some maintenance operations on the
> duplicate db to see if this is the problem, but if it runs slow on
> all the databases it isn't a problem of indexing or something like
> this ).
>
By adding a second database (for testing?) you are putting further
strain on you memory resources. Why clog up the cache with duplicate
data from a second database?
You may want to update statistics on the database in question. It's not
clear what's going on, but it is clear that you are using your server
for file services and a domain controller and SQL Server and only have
1.2GB RAM. That may be enough (don't know your network and SQL specs),
but why not add more memory since it's so cheap. Putting 2GB RAM in
there would give you a little more headroom and then you could give the
server a little more than 400MB (which is not very much for a server).
David Gugick
Imceda Software
www.imceda.com
|||I don't think the problem is related to how much memory is in the server,
since 4 months ago when the server had only 512MB of RAM everything was ok.
Also, I don't think the problem is related to how much memory is in the
server because the same thing is happening when there is a lot of unused
memory ( say sql with 400MB and the rest for the system - which is a lot ).
I think we got to focus on the main issue: why on all the databases is
working ok, and only on one of them is working slowly after a couple of
days.
thanks for reply
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:e2EoHkm5EHA.3756@.TK2MSFTNGP14.phx.gbl...
> Fatboyslimro wrote:
> By adding a second database (for testing?) you are putting further
> strain on you memory resources. Why clog up the cache with duplicate
> data from a second database?
> You may want to update statistics on the database in question. It's not
> clear what's going on, but it is clear that you are using your server
> for file services and a domain controller and SQL Server and only have
> 1.2GB RAM. That may be enough (don't know your network and SQL specs),
> but why not add more memory since it's so cheap. Putting 2GB RAM in
> there would give you a little more headroom and then you could give the
> server a little more than 400MB (which is not very much for a server).
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

performance problem

every 3-4 days I have to restart the sql service because some users are
complaining of poor performance in a specific form ( where they are
intensively add some small info but constantly with speed, and the process
of introduction is always behind where it should be ).
I made a test db where I restored the original db and I noticed that it's
working with normal speed in this parallel database.
My initial supposition was that it should work with the same low speed in
this new database, but it doesn't.
So I think this situation it's somewhat related with the database that it's
intensively worked with, and not with the memory of the server.
In 3-4 days the sql server process grows to about 500MB and if I let the
process for a week it goes to 800MB.
So to resume :
there is poor performance in 3-4 days from restart on a database that is
intensively worked on ,
but a normal performance on a duplicate database on the same server.
The server is a P4 at 2GHz with 1280MB of RAM and a scsi hard disk for
storing the databases.
Thanks for any cluesFatboyslimro wrote:
> every 3-4 days I have to restart the sql service because some users
> are complaining of poor performance in a specific form ( where they
> are intensively add some small info but constantly with speed, and
> the process of introduction is always behind where it should be ).
> I made a test db where I restored the original db and I noticed that
> it's working with normal speed in this parallel database.
> My initial supposition was that it should work with the same low
> speed in this new database, but it doesn't.
> So I think this situation it's somewhat related with the database
> that it's intensively worked with, and not with the memory of the
> server.
> In 3-4 days the sql server process grows to about 500MB and if I let
> the process for a week it goes to 800MB.
>
> So to resume :
> there is poor performance in 3-4 days from restart on a database that
> is intensively worked on ,
> but a normal performance on a duplicate database on the same server.
>
> The server is a P4 at 2GHz with 1280MB of RAM and a scsi hard disk for
> storing the databases.
> Thanks for any clues
Memory issue sounds fine. SQL Server uses memory as needed up to its set
maximum or what's available in the OS and does not release memory
without a struggle. As more data is read into cache, the memory
footprint will increase. If you are running into a situation where the
OS needs more memory or you have other applications running on the same
server (lie IIS) and they are struggling, you need to set SQL Server to
only use a maximum amount of memory. You can do this easily from SQL
Enterprise Manager.
There could be other problems that are causing the slowness, however.
Report back to us whether you have other application running on the
server besides SQL Server.
David Gugick
Imceda Software
www.imceda.com|||The server is also a file server and a DC.
But the thing is that in the same time that this database is running slow,
on a duplicate database on the same server the applications are running ok.
the sql server is limited to 800MB of RAM from a total of 1280MB
( before I made this duplicate database I thought that when it's going slow
on a database it will run slow on all the databases on the server - and I
wanted to do some maintenance operations on the duplicate db to see if this
is the problem, but if it runs slow on all the databases it isn't a problem
of indexing or something like this ).
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:e7H8kE64EHA.2964@.TK2MSFTNGP15.phx.gbl...
> Fatboyslimro wrote:
> > every 3-4 days I have to restart the sql service because some users
> > are complaining of poor performance in a specific form ( where they
> > are intensively add some small info but constantly with speed, and
> > the process of introduction is always behind where it should be ).
> > I made a test db where I restored the original db and I noticed that
> > it's working with normal speed in this parallel database.
> > My initial supposition was that it should work with the same low
> > speed in this new database, but it doesn't.
> > So I think this situation it's somewhat related with the database
> > that it's intensively worked with, and not with the memory of the
> > server.
> > In 3-4 days the sql server process grows to about 500MB and if I let
> > the process for a week it goes to 800MB.
> >
> >
> > So to resume :
> >
> > there is poor performance in 3-4 days from restart on a database that
> > is intensively worked on ,
> > but a normal performance on a duplicate database on the same server.
> >
> >
> > The server is a P4 at 2GHz with 1280MB of RAM and a scsi hard disk for
> > storing the databases.
> >
> > Thanks for any clues
> Memory issue sounds fine. SQL Server uses memory as needed up to its set
> maximum or what's available in the OS and does not release memory
> without a struggle. As more data is read into cache, the memory
> footprint will increase. If you are running into a situation where the
> OS needs more memory or you have other applications running on the same
> server (lie IIS) and they are struggling, you need to set SQL Server to
> only use a maximum amount of memory. You can do this easily from SQL
> Enterprise Manager.
> There could be other problems that are causing the slowness, however.
> Report back to us whether you have other application running on the
> server besides SQL Server.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Fatboyslimro wrote:
> The server is also a file server and a DC.
> But the thing is that in the same time that this database is running
> slow, on a duplicate database on the same server the applications are
> running ok.
> the sql server is limited to 800MB of RAM from a total of 1280MB
> ( before I made this duplicate database I thought that when it's
> going slow on a database it will run slow on all the databases on the
> server - and I wanted to do some maintenance operations on the
> duplicate db to see if this is the problem, but if it runs slow on
> all the databases it isn't a problem of indexing or something like
> this ).
>
By adding a second database (for testing?) you are putting further
strain on you memory resources. Why clog up the cache with duplicate
data from a second database?
You may want to update statistics on the database in question. It's not
clear what's going on, but it is clear that you are using your server
for file services and a domain controller and SQL Server and only have
1.2GB RAM. That may be enough (don't know your network and SQL specs),
but why not add more memory since it's so cheap. Putting 2GB RAM in
there would give you a little more headroom and then you could give the
server a little more than 400MB (which is not very much for a server).
David Gugick
Imceda Software
www.imceda.com|||I don't think the problem is related to how much memory is in the server,
since 4 months ago when the server had only 512MB of RAM everything was ok.
Also, I don't think the problem is related to how much memory is in the
server because the same thing is happening when there is a lot of unused
memory ( say sql with 400MB and the rest for the system - which is a lot ).
I think we got to focus on the main issue: why on all the databases is
working ok, and only on one of them is working slowly after a couple of
days.
thanks for reply
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:e2EoHkm5EHA.3756@.TK2MSFTNGP14.phx.gbl...
> Fatboyslimro wrote:
> > The server is also a file server and a DC.
> >
> > But the thing is that in the same time that this database is running
> > slow, on a duplicate database on the same server the applications are
> > running ok.
> >
> > the sql server is limited to 800MB of RAM from a total of 1280MB
> >
> > ( before I made this duplicate database I thought that when it's
> > going slow on a database it will run slow on all the databases on the
> > server - and I wanted to do some maintenance operations on the
> > duplicate db to see if this is the problem, but if it runs slow on
> > all the databases it isn't a problem of indexing or something like
> > this ).
> >
> By adding a second database (for testing?) you are putting further
> strain on you memory resources. Why clog up the cache with duplicate
> data from a second database?
> You may want to update statistics on the database in question. It's not
> clear what's going on, but it is clear that you are using your server
> for file services and a domain controller and SQL Server and only have
> 1.2GB RAM. That may be enough (don't know your network and SQL specs),
> but why not add more memory since it's so cheap. Putting 2GB RAM in
> there would give you a little more headroom and then you could give the
> server a little more than 400MB (which is not very much for a server).
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>