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 Monitor SQL Counters question

I'm a bit confused, so I hope someone can help.
Under the MSSQL$AAP1:Databases Object are two counters. Data File(s) Size
(KB) and Log File(s) Size (KB). If I understand these correctly, they are to
monitor spikes within the growth of the data & log files. However, when I
run them against my Production Server, they are both consistantly at 100%.
HUH?
The disk drive has more than enough space and both files for my database (I
chose to monitor a single DB, not all of them) are set to AutoGrow. So, why
do the counters register at the top of the charts?
If anyone could explain this for me, I'd greatly appreciate it. Thanks,
Catadmin
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
Hi
I am not sure you are interpreting the counter correctly, with all
performance monitor counters you need to look at the scale to get the size.
Being 100% of the size all the time would indicate that there has been no
growth during that period.
If you do a period sp_helpfile and store the results into a table you should
be able to monitor store the current size over time.
John
"Catadmin" wrote:

> I'm a bit confused, so I hope someone can help.
> Under the MSSQL$AAP1:Databases Object are two counters. Data File(s) Size
> (KB) and Log File(s) Size (KB). If I understand these correctly, they are to
> monitor spikes within the growth of the data & log files. However, when I
> run them against my Production Server, they are both consistantly at 100%.
> HUH?
> The disk drive has more than enough space and both files for my database (I
> chose to monitor a single DB, not all of them) are set to AutoGrow. So, why
> do the counters register at the top of the charts?
> If anyone could explain this for me, I'd greatly appreciate it. Thanks,
> Catadmin
> --
> MCDBA, MCSA
> Random Thoughts: If a person is Microsoft Certified, does that mean that
> Microsoft pays the bills for the funny white jackets that tie in the back?
> @.=)
|||So how does one know when the files do change in size? Does the percentage
go down? Or is there a number higher than 100 that it shoots up to during a
"growth spurt"?
Thanks,
Catadmin
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> I am not sure you are interpreting the counter correctly, with all
> performance monitor counters you need to look at the scale to get the size.
> Being 100% of the size all the time would indicate that there has been no
> growth during that period.
> If you do a period sp_helpfile and store the results into a table you should
> be able to monitor store the current size over time.
> John
> "Catadmin" wrote:
|||Hi
If open the performance data file in perfmon you will see a step when the
file grows.
John
"Catadmin" wrote:
[vbcol=seagreen]
> So how does one know when the files do change in size? Does the percentage
> go down? Or is there a number higher than 100 that it shoots up to during a
> "growth spurt"?
> Thanks,
> Catadmin
>
> "John Bell" wrote:

Performance Monitor SQL Counters question

I'm a bit confused, so I hope someone can help.
Under the MSSQL$AAP1:Databases Object are two counters. Data File(s) Size
(KB) and Log File(s) Size (KB). If I understand these correctly, they are t
o
monitor spikes within the growth of the data & log files. However, when I
run them against my Production Server, they are both consistantly at 100%.
HUH?
The disk drive has more than enough space and both files for my database (I
chose to monitor a single DB, not all of them) are set to AutoGrow. So, why
do the counters register at the top of the charts?
If anyone could explain this for me, I'd greatly appreciate it. Thanks,
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)Hi
I am not sure you are interpreting the counter correctly, with all
performance monitor counters you need to look at the scale to get the size.
Being 100% of the size all the time would indicate that there has been no
growth during that period.
If you do a period sp_helpfile and store the results into a table you should
be able to monitor store the current size over time.
John
"Catadmin" wrote:

> I'm a bit confused, so I hope someone can help.
> Under the MSSQL$AAP1:Databases Object are two counters. Data File(s) Size
> (KB) and Log File(s) Size (KB). If I understand these correctly, they are
to
> monitor spikes within the growth of the data & log files. However, when I
> run them against my Production Server, they are both consistantly at 100%.
> HUH?
> The disk drive has more than enough space and both files for my database (
I
> chose to monitor a single DB, not all of them) are set to AutoGrow. So, w
hy
> do the counters register at the top of the charts?
> If anyone could explain this for me, I'd greatly appreciate it. Thanks,
> Catadmin
> --
> MCDBA, MCSA
> Random Thoughts: If a person is Microsoft Certified, does that mean that
> Microsoft pays the bills for the funny white jackets that tie in the back?
'
> @.=)|||So how does one know when the files do change in size? Does the percentage
go down? Or is there a number higher than 100 that it shoots up to during a
"growth spurt"?
Thanks,
Catadmin
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> I am not sure you are interpreting the counter correctly, with all
> performance monitor counters you need to look at the scale to get the size
.
> Being 100% of the size all the time would indicate that there has been no
> growth during that period.
> If you do a period sp_helpfile and store the results into a table you shou
ld
> be able to monitor store the current size over time.
> John
> "Catadmin" wrote:
>|||Hi
If open the performance data file in perfmon you will see a step when the
file grows.
John
"Catadmin" wrote:
[vbcol=seagreen]
> So how does one know when the files do change in size? Does the percentag
e
> go down? Or is there a number higher than 100 that it shoots up to during
a
> "growth spurt"?
> Thanks,
> Catadmin
>
> "John Bell" wrote:
>