Performance problem

need help ASAP on this.
I had a stored procedure that pulls the "bookings" for our company
(sales, if you will) for the past year. Before, it looked like this:
SELECT UKDR, VANR, VMGS, VPR1, UDAT, VERA, UBLN,VMGL, ReportingGroup,
UVT1, SLNAME, UVT2, AXTXT, USL1
FROM (((bdg_view_GroupingByReceivableCustomer
INNER JOIN TXUYUV00 ON
bdg_view_GroupingByReceivableCustomer.KKDR = TXUYUV00.UKDR) INNER JOIN
TXUYUF01 ON TXUYUV00.UBLN = TXUYUF01.VBLN and
TXUYUV00.UBLA=TXUYUF01.VBLA AND TXUYUV00.UBNR=TXUYUF01.VBNR AND
TXUYUV00.UPLT = TXUYUF01.VPLT) INNER JOIN TXMYTX00 ON
'#V'+TXUYUV00.UVT2 = TXMYTX00.AXANR AND TXUYUV00.UPLT=TXMYTX00.AXPLT)
LEFT OUTER JOIN CSPSLS00 on TXUYUV00.UVT1 = CSPSLS00.SLSMAN
WHERE
UBLA<>'JB' AND bdg_view_GroupingByReceivableCustomer.ReportingGroup <>
'3' AND TXUYUF01.VDAT >= 20050926 AND TXUYUF01.VDAT <= 20060924 AND
TXUYUF01.VANR NOT LIKE 'ZZ%' AND
bdg_view_GroupingByReceivableCustomer.ReportingGroup <> '5' AND
TXUYUV00.USL1 NOT IN('PL','CO','FB','VW','VR','FC','CD','F
F','TR')
It was running fine. It was a little slow, but not too bad.
Fastforward to today. We added a new unit to our company, that now
requires a second value for fields ending in "BNR" (VBNR, UBNR, etc.)
Now the query looks like this:
SELECT UKDR, VANR, VMGS, VPR1, UDAT, VERA, UBLN,VMGL, ReportingGroup,
UVT1, SLNAME, UVT2, AXTXT, USL1
FROM (((bdg_view_GroupingByReceivableCustomer
INNER JOIN TXUYUV00 ON
bdg_view_GroupingByReceivableCustomer.KKDR = TXUYUV00.UKDR) INNER JOIN
TXUYUF01 ON TXUYUV00.UBLN = TXUYUF01.VBLN and
TXUYUV00.UBLA=TXUYUF01.VBLA AND TXUYUV00.UBNR=TXUYUF01.VBNR AND
TXUYUV00.UPLT = TXUYUF01.VPLT) INNER JOIN TXMYTX00 ON
'#V'+TXUYUV00.UVT2 = TXMYTX00.AXANR AND TXUYUV00.UBNR = TXMYTX00.AXBNR
and TXUYUV00.UPLT=TXMYTX00.AXPLT) LEFT OUTER JOIN CSPSLS00 on
TXUYUV00.UVT1 = CSPSLS00.SLSMAN
WHERE
UBLA<>'JB' AND bdg_view_GroupingByReceivableCustomer.ReportingGroup <>
'3' AND TXUYUF01.VDAT >= 20050926 AND TXUYUF01.VDAT <= 20060924 AND
TXUYUF01.VANR NOT LIKE 'ZZ%' AND
bdg_view_GroupingByReceivableCustomer.ReportingGroup <> '5' AND
TXUYUV00.USL1 NOT IN('PL','CO','FB','VW','VR','FC','CD','F
F','TR') and
UBNR = '001'
Problem? Though these tables have changed only modestly since this
changeover (number of rows), the query takes 5 minutes to complete, and
the CPU usage on a dual-processor server goes to 100%. I can't even
bring up the task manager until the query completes. This wasn't a
problem with the old procedure.
Can someone help me figure this out and what I need to do?
UPDATE: I have redone the query to look like this as per some other
people in the SQL Server Central forum
I have put a non-clustered index on the table TXUYUV00 involving UBLA,
UBNR and USL1, and have redone the query to this:
SELECT UKDR, VANR, VMGS, VPR1, UDAT, VERA, UBLN,VMGL, ReportingGroup,
UVT1, SLNAME, UVT2, AXTXT, USL1
FROM (((bdg_view_GroupingByReceivableCustomer
INNER JOIN TXUYUV00 ON
bdg_view_GroupingByReceivableCustomer.KKDR = TXUYUV00.UKDR) INNER JOIN
TXUYUF01 ON TXUYUV00.UBLN = TXUYUF01.VBLN and
TXUYUV00.UBLA=TXUYUF01.VBLA AND TXUYUV00.UBNR=TXUYUF01.VBNR AND
TXUYUV00.UPLT = TXUYUF01.VPLT AND TXUYUV00.UBNR = '001' and
TXUYUV00.UBLA IN('SA','SO') and TXUYUV00.USL1 NOT
IN('PL','CO','FB','VW','VR','FC','CD','F
F','TR') ) INNER JOIN TXMYTX00
ON '#V'+TXUYUV00.UVT2 = TXMYTX00.AXANR AND TXUYUV00.UBNR =
TXMYTX00.AXBNR and TXUYUV00.UPLT=TXMYTX00.AXPLT) LEFT OUTER JOIN
CSPSLS00 on TXUYUV00.UVT1 = CSPSLS00.SLSMAN
WHERE
bdg_view_GroupingByReceivableCustomer.ReportingGroup <> '3' AND
TXUYUF01.VDAT >= 20050926 AND TXUYUF01.VDAT <= 20060924 AND
TXUYUF01.VANR NOT LIKE 'ZZ%' AND
bdg_view_GroupingByReceivableCustomer.ReportingGroup <> '5'
It still runs and runs and takes a ton of CPU time. The Clustered
Index s is taking 49% of the execution plan. When I run the query,
I have to stop it immediately because it makes the CPU go to 90% and
the system stays there.
It didn't used to do this. I can't figure out why it does now.Which version of SQL are you using? 2000 or 2005?
Regards
Colin Dawson
www.cjdawson.com
"Brent White" <bwhite@.badgersportswear.com> wrote in message
news:1147719197.120509.118220@.j33g2000cwa.googlegroups.com...
> need help ASAP on this.
> I had a stored procedure that pulls the "bookings" for our company
> (sales, if you will) for the past year. Before, it looked like this:
>
> SELECT UKDR, VANR, VMGS, VPR1, UDAT, VERA, UBLN,VMGL, ReportingGroup,
> UVT1, SLNAME, UVT2, AXTXT, USL1
> FROM (((bdg_view_GroupingByReceivableCustomer
INNER JOIN TXUYUV00 ON
> bdg_view_GroupingByReceivableCustomer.KKDR = TXUYUV00.UKDR) INNER JOIN
> TXUYUF01 ON TXUYUV00.UBLN = TXUYUF01.VBLN and
> TXUYUV00.UBLA=TXUYUF01.VBLA AND TXUYUV00.UBNR=TXUYUF01.VBNR AND
> TXUYUV00.UPLT = TXUYUF01.VPLT) INNER JOIN TXMYTX00 ON
> '#V'+TXUYUV00.UVT2 = TXMYTX00.AXANR AND TXUYUV00.UPLT=TXMYTX00.AXPLT)
> LEFT OUTER JOIN CSPSLS00 on TXUYUV00.UVT1 = CSPSLS00.SLSMAN
> WHERE
> UBLA<>'JB' AND bdg_view_GroupingByReceivableCustomer.ReportingGroup <>
> '3' AND TXUYUF01.VDAT >= 20050926 AND TXUYUF01.VDAT <= 20060924 AND
> TXUYUF01.VANR NOT LIKE 'ZZ%' AND
> bdg_view_GroupingByReceivableCustomer.ReportingGroup <> '5' AND
> TXUYUV00.USL1 NOT IN('PL','CO','FB','VW','VR','FC','CD','F
F','TR')
> It was running fine. It was a little slow, but not too bad.
> Fastforward to today. We added a new unit to our company, that now
> requires a second value for fields ending in "BNR" (VBNR, UBNR, etc.)
> Now the query looks like this:
>
> SELECT UKDR, VANR, VMGS, VPR1, UDAT, VERA, UBLN,VMGL, ReportingGroup,
> UVT1, SLNAME, UVT2, AXTXT, USL1
> FROM (((bdg_view_GroupingByReceivableCustomer
INNER JOIN TXUYUV00 ON
> bdg_view_GroupingByReceivableCustomer.KKDR = TXUYUV00.UKDR) INNER JOIN
> TXUYUF01 ON TXUYUV00.UBLN = TXUYUF01.VBLN and
> TXUYUV00.UBLA=TXUYUF01.VBLA AND TXUYUV00.UBNR=TXUYUF01.VBNR AND
> TXUYUV00.UPLT = TXUYUF01.VPLT) INNER JOIN TXMYTX00 ON
> '#V'+TXUYUV00.UVT2 = TXMYTX00.AXANR AND TXUYUV00.UBNR = TXMYTX00.AXBNR
> and TXUYUV00.UPLT=TXMYTX00.AXPLT) LEFT OUTER JOIN CSPSLS00 on
> TXUYUV00.UVT1 = CSPSLS00.SLSMAN
> WHERE
> UBLA<>'JB' AND bdg_view_GroupingByReceivableCustomer.ReportingGroup <>
> '3' AND TXUYUF01.VDAT >= 20050926 AND TXUYUF01.VDAT <= 20060924 AND
> TXUYUF01.VANR NOT LIKE 'ZZ%' AND
> bdg_view_GroupingByReceivableCustomer.ReportingGroup <> '5' AND
> TXUYUV00.USL1 NOT IN('PL','CO','FB','VW','VR','FC','CD','F
F','TR') and
> UBNR = '001'
>
> Problem? Though these tables have changed only modestly since this
> changeover (number of rows), the query takes 5 minutes to complete, and
> the CPU usage on a dual-processor server goes to 100%. I can't even
> bring up the task manager until the query completes. This wasn't a
> problem with the old procedure.
> Can someone help me figure this out and what I need to do?
>
>
> UPDATE: I have redone the query to look like this as per some other
> people in the SQL Server Central forum
>
> I have put a non-clustered index on the table TXUYUV00 involving UBLA,
> UBNR and USL1, and have redone the query to this:
> SELECT UKDR, VANR, VMGS, VPR1, UDAT, VERA, UBLN,VMGL, ReportingGroup,
> UVT1, SLNAME, UVT2, AXTXT, USL1
> FROM (((bdg_view_GroupingByReceivableCustomer
INNER JOIN TXUYUV00 ON
> bdg_view_GroupingByReceivableCustomer.KKDR = TXUYUV00.UKDR) INNER JOIN
> TXUYUF01 ON TXUYUV00.UBLN = TXUYUF01.VBLN and
> TXUYUV00.UBLA=TXUYUF01.VBLA AND TXUYUV00.UBNR=TXUYUF01.VBNR AND
> TXUYUV00.UPLT = TXUYUF01.VPLT AND TXUYUV00.UBNR = '001' and
> TXUYUV00.UBLA IN('SA','SO') and TXUYUV00.USL1 NOT
> IN('PL','CO','FB','VW','VR','FC','CD','F
F','TR') ) INNER JOIN TXMYTX00
> ON '#V'+TXUYUV00.UVT2 = TXMYTX00.AXANR AND TXUYUV00.UBNR =
> TXMYTX00.AXBNR and TXUYUV00.UPLT=TXMYTX00.AXPLT) LEFT OUTER JOIN
> CSPSLS00 on TXUYUV00.UVT1 = CSPSLS00.SLSMAN
> WHERE
> bdg_view_GroupingByReceivableCustomer.ReportingGroup <> '3' AND
> TXUYUF01.VDAT >= 20050926 AND TXUYUF01.VDAT <= 20060924 AND
> TXUYUF01.VANR NOT LIKE 'ZZ%' AND
> bdg_view_GroupingByReceivableCustomer.ReportingGroup <> '5'
>
> It still runs and runs and takes a ton of CPU time. The Clustered
> Index s is taking 49% of the execution plan. When I run the query,
> I have to stop it immediately because it makes the CPU go to 90% and
> the system stays there.
> It didn't used to do this. I can't figure out why it does now.
>|||Ok. I've just taken a very quick look at this select statement and it'll be
very difficult to help without more information.
Here's what we'll need to know...
1. what version of SQL server are you using?
2. what tables to does is column come from? Please modify the sql that I've
attached below to include table alias's for all the columns. It'll help
when it comes to re-jitting the code to make it more efficient.
3. what does the execution plan look like? Please include a text based
execution place (use set showplan on to get it)
4. can you include ddl and some example data so that we'll be able to
actually test the the procedure. It's important because we don't know what
datatypes these columns are.
I've cleaned up the second SQL statement to help others decipher what's
happening, here's the code...
SELECT
UKDR,
VANR,
VMGS,
VPR1,
UDAT,
VERA,
UBLN,
VMGL,
ReportingGroup,
UVT1,
SLNAME,
UVT2,
AXTXT,
USL1
FROM (((bdg_view_GroupingByReceivableCustomer
INNER JOIN TXUYUV00 ON bdg_view_GroupingByReceivableCustomer.KKDR =
TXUYUV00.UKDR)
INNER JOIN TXUYUF01 ON TXUYUV00.UBLN = TXUYUF01.VBLN
and TXUYUV00.UBLA=TXUYUF01.VBLA
AND TXUYUV00.UBNR=TXUYUF01.VBNR
AND TXUYUV00.UPLT = TXUYUF01.VPLT )
INNER JOIN TXMYTX00 ON '#V'+TXUYUV00.UVT2 = TXMYTX00.AXANR
AND TXUYUV00.UBNR = TXMYTX00.AXBNR
and TXUYUV00.UPLT=TXMYTX00.AXPLT)
LEFT OUTER JOIN CSPSLS00 on TXUYUV00.UVT1 = CSPSLS00.SLSMAN
WHERE UBLA<>'JB'
AND bdg_view_GroupingByReceivableCustomer.ReportingGroup <> '3'
AND TXUYUF01.VDAT >= 20050926
AND TXUYUF01.VDAT <= 20060924
AND TXUYUF01.VANR NOT LIKE 'ZZ%'
AND bdg_view_GroupingByReceivableCustomer.ReportingGroup <> '5'
AND TXUYUV00.USL1 NOT IN('PL','CO','FB','VW','VR','FC','CD','F
F','TR')
and UBNR = '001'
Regards
Colin Dawson
www.cjdawson.com
"Brent White" <bwhite@.badgersportswear.com> wrote in message
news:1147719197.120509.118220@.j33g2000cwa.googlegroups.com...
> need help ASAP on this.
> I had a stored procedure that pulls the "bookings" for our company
> (sales, if you will) for the past year. Before, it looked like this:
>
> SELECT UKDR, VANR, VMGS, VPR1, UDAT, VERA, UBLN,VMGL, ReportingGroup,
> UVT1, SLNAME, UVT2, AXTXT, USL1
> FROM (((bdg_view_GroupingByReceivableCustomer
INNER JOIN TXUYUV00 ON
> bdg_view_GroupingByReceivableCustomer.KKDR = TXUYUV00.UKDR) INNER JOIN
> TXUYUF01 ON TXUYUV00.UBLN = TXUYUF01.VBLN and
> TXUYUV00.UBLA=TXUYUF01.VBLA AND TXUYUV00.UBNR=TXUYUF01.VBNR AND
> TXUYUV00.UPLT = TXUYUF01.VPLT) INNER JOIN TXMYTX00 ON
> '#V'+TXUYUV00.UVT2 = TXMYTX00.AXANR AND TXUYUV00.UPLT=TXMYTX00.AXPLT)
> LEFT OUTER JOIN CSPSLS00 on TXUYUV00.UVT1 = CSPSLS00.SLSMAN
> WHERE
> UBLA<>'JB' AND bdg_view_GroupingByReceivableCustomer.ReportingGroup <>
> '3' AND TXUYUF01.VDAT >= 20050926 AND TXUYUF01.VDAT <= 20060924 AND
> TXUYUF01.VANR NOT LIKE 'ZZ%' AND
> bdg_view_GroupingByReceivableCustomer.ReportingGroup <> '5' AND
> TXUYUV00.USL1 NOT IN('PL','CO','FB','VW','VR','FC','CD','F
F','TR')
> It was running fine. It was a little slow, but not too bad.
> Fastforward to today. We added a new unit to our company, that now
> requires a second value for fields ending in "BNR" (VBNR, UBNR, etc.)
> Now the query looks like this:
>
> SELECT UKDR, VANR, VMGS, VPR1, UDAT, VERA, UBLN,VMGL, ReportingGroup,
> UVT1, SLNAME, UVT2, AXTXT, USL1
> FROM (((bdg_view_GroupingByReceivableCustomer
INNER JOIN TXUYUV00 ON
> bdg_view_GroupingByReceivableCustomer.KKDR = TXUYUV00.UKDR) INNER JOIN
> TXUYUF01 ON TXUYUV00.UBLN = TXUYUF01.VBLN and
> TXUYUV00.UBLA=TXUYUF01.VBLA AND TXUYUV00.UBNR=TXUYUF01.VBNR AND
> TXUYUV00.UPLT = TXUYUF01.VPLT) INNER JOIN TXMYTX00 ON
> '#V'+TXUYUV00.UVT2 = TXMYTX00.AXANR AND TXUYUV00.UBNR = TXMYTX00.AXBNR
> and TXUYUV00.UPLT=TXMYTX00.AXPLT) LEFT OUTER JOIN CSPSLS00 on
> TXUYUV00.UVT1 = CSPSLS00.SLSMAN
> WHERE
> UBLA<>'JB' AND bdg_view_GroupingByReceivableCustomer.ReportingGroup <>
> '3' AND TXUYUF01.VDAT >= 20050926 AND TXUYUF01.VDAT <= 20060924 AND
> TXUYUF01.VANR NOT LIKE 'ZZ%' AND
> bdg_view_GroupingByReceivableCustomer.ReportingGroup <> '5' AND
> TXUYUV00.USL1 NOT IN('PL','CO','FB','VW','VR','FC','CD','F
F','TR') and
> UBNR = '001'
>
> Problem? Though these tables have changed only modestly since this
> changeover (number of rows), the query takes 5 minutes to complete, and
> the CPU usage on a dual-processor server goes to 100%. I can't even
> bring up the task manager until the query completes. This wasn't a
> problem with the old procedure.
> Can someone help me figure this out and what I need to do?
>
>
> UPDATE: I have redone the query to look like this as per some other
> people in the SQL Server Central forum
>
> I have put a non-clustered index on the table TXUYUV00 involving UBLA,
> UBNR and USL1, and have redone the query to this:
> SELECT UKDR, VANR, VMGS, VPR1, UDAT, VERA, UBLN,VMGL, ReportingGroup,
> UVT1, SLNAME, UVT2, AXTXT, USL1
> FROM (((bdg_view_GroupingByReceivableCustomer
INNER JOIN TXUYUV00 ON
> bdg_view_GroupingByReceivableCustomer.KKDR = TXUYUV00.UKDR) INNER JOIN
> TXUYUF01 ON TXUYUV00.UBLN = TXUYUF01.VBLN and
> TXUYUV00.UBLA=TXUYUF01.VBLA AND TXUYUV00.UBNR=TXUYUF01.VBNR AND
> TXUYUV00.UPLT = TXUYUF01.VPLT AND TXUYUV00.UBNR = '001' and
> TXUYUV00.UBLA IN('SA','SO') and TXUYUV00.USL1 NOT
> IN('PL','CO','FB','VW','VR','FC','CD','F
F','TR') ) INNER JOIN TXMYTX00
> ON '#V'+TXUYUV00.UVT2 = TXMYTX00.AXANR AND TXUYUV00.UBNR =
> TXMYTX00.AXBNR and TXUYUV00.UPLT=TXMYTX00.AXPLT) LEFT OUTER JOIN
> CSPSLS00 on TXUYUV00.UVT1 = CSPSLS00.SLSMAN
> WHERE
> bdg_view_GroupingByReceivableCustomer.ReportingGroup <> '3' AND
> TXUYUF01.VDAT >= 20050926 AND TXUYUF01.VDAT <= 20060924 AND
> TXUYUF01.VANR NOT LIKE 'ZZ%' AND
> bdg_view_GroupingByReceivableCustomer.ReportingGroup <> '5'
>
> It still runs and runs and takes a ton of CPU time. The Clustered
> Index s is taking 49% of the execution plan. When I run the query,
> I have to stop it immediately because it makes the CPU go to 90% and
> the system stays there.
> It didn't used to do this. I can't figure out why it does now.
>|||> I have put a non-clustered index on the table TXUYUV00 involving UBLA,
> UBNR and USL1, and have redone the query to this:
Try creating this same index on the other tables as well. You are joining
to several tables using these columns, and you need the index on all of
them. Put the column with the most distinct values first in your index, and
the column with the fewest distinct values last. I think this will speed
things up.
However, it is hard to say without proper DDL. Post DDL showing your
tables, keys, and indexes, and we may be able to give a better answer.
"Brent White" <bwhite@.badgersportswear.com> wrote in message
news:1147719197.120509.118220@.j33g2000cwa.googlegroups.com...
> need help ASAP on this.
> I had a stored procedure that pulls the "bookings" for our company
> (sales, if you will) for the past year. Before, it looked like this:
>
> SELECT UKDR, VANR, VMGS, VPR1, UDAT, VERA, UBLN,VMGL, ReportingGroup,
> UVT1, SLNAME, UVT2, AXTXT, USL1
> FROM (((bdg_view_GroupingByReceivableCustomer
INNER JOIN TXUYUV00 ON
> bdg_view_GroupingByReceivableCustomer.KKDR = TXUYUV00.UKDR) INNER JOIN
> TXUYUF01 ON TXUYUV00.UBLN = TXUYUF01.VBLN and
> TXUYUV00.UBLA=TXUYUF01.VBLA AND TXUYUV00.UBNR=TXUYUF01.VBNR AND
> TXUYUV00.UPLT = TXUYUF01.VPLT) INNER JOIN TXMYTX00 ON
> '#V'+TXUYUV00.UVT2 = TXMYTX00.AXANR AND TXUYUV00.UPLT=TXMYTX00.AXPLT)
> LEFT OUTER JOIN CSPSLS00 on TXUYUV00.UVT1 = CSPSLS00.SLSMAN
> WHERE
> UBLA<>'JB' AND bdg_view_GroupingByReceivableCustomer.ReportingGroup <>
> '3' AND TXUYUF01.VDAT >= 20050926 AND TXUYUF01.VDAT <= 20060924 AND
> TXUYUF01.VANR NOT LIKE 'ZZ%' AND
> bdg_view_GroupingByReceivableCustomer.ReportingGroup <> '5' AND
> TXUYUV00.USL1 NOT IN('PL','CO','FB','VW','VR','FC','CD','F
F','TR')
> It was running fine. It was a little slow, but not too bad.
> Fastforward to today. We added a new unit to our company, that now
> requires a second value for fields ending in "BNR" (VBNR, UBNR, etc.)
> Now the query looks like this:
>
> SELECT UKDR, VANR, VMGS, VPR1, UDAT, VERA, UBLN,VMGL, ReportingGroup,
> UVT1, SLNAME, UVT2, AXTXT, USL1
> FROM (((bdg_view_GroupingByReceivableCustomer
INNER JOIN TXUYUV00 ON
> bdg_view_GroupingByReceivableCustomer.KKDR = TXUYUV00.UKDR) INNER JOIN
> TXUYUF01 ON TXUYUV00.UBLN = TXUYUF01.VBLN and
> TXUYUV00.UBLA=TXUYUF01.VBLA AND TXUYUV00.UBNR=TXUYUF01.VBNR AND
> TXUYUV00.UPLT = TXUYUF01.VPLT) INNER JOIN TXMYTX00 ON
> '#V'+TXUYUV00.UVT2 = TXMYTX00.AXANR AND TXUYUV00.UBNR = TXMYTX00.AXBNR
> and TXUYUV00.UPLT=TXMYTX00.AXPLT) LEFT OUTER JOIN CSPSLS00 on
> TXUYUV00.UVT1 = CSPSLS00.SLSMAN
> WHERE
> UBLA<>'JB' AND bdg_view_GroupingByReceivableCustomer.ReportingGroup <>
> '3' AND TXUYUF01.VDAT >= 20050926 AND TXUYUF01.VDAT <= 20060924 AND
> TXUYUF01.VANR NOT LIKE 'ZZ%' AND
> bdg_view_GroupingByReceivableCustomer.ReportingGroup <> '5' AND
> TXUYUV00.USL1 NOT IN('PL','CO','FB','VW','VR','FC','CD','F
F','TR') and
> UBNR = '001'
>
> Problem? Though these tables have changed only modestly since this
> changeover (number of rows), the query takes 5 minutes to complete, and
> the CPU usage on a dual-processor server goes to 100%. I can't even
> bring up the task manager until the query completes. This wasn't a
> problem with the old procedure.
> Can someone help me figure this out and what I need to do?
>
>
> UPDATE: I have redone the query to look like this as per some other
> people in the SQL Server Central forum
>
> I have put a non-clustered index on the table TXUYUV00 involving UBLA,
> UBNR and USL1, and have redone the query to this:
> SELECT UKDR, VANR, VMGS, VPR1, UDAT, VERA, UBLN,VMGL, ReportingGroup,
> UVT1, SLNAME, UVT2, AXTXT, USL1
> FROM (((bdg_view_GroupingByReceivableCustomer
INNER JOIN TXUYUV00 ON
> bdg_view_GroupingByReceivableCustomer.KKDR = TXUYUV00.UKDR) INNER JOIN
> TXUYUF01 ON TXUYUV00.UBLN = TXUYUF01.VBLN and
> TXUYUV00.UBLA=TXUYUF01.VBLA AND TXUYUV00.UBNR=TXUYUF01.VBNR AND
> TXUYUV00.UPLT = TXUYUF01.VPLT AND TXUYUV00.UBNR = '001' and
> TXUYUV00.UBLA IN('SA','SO') and TXUYUV00.USL1 NOT
> IN('PL','CO','FB','VW','VR','FC','CD','F
F','TR') ) INNER JOIN TXMYTX00
> ON '#V'+TXUYUV00.UVT2 = TXMYTX00.AXANR AND TXUYUV00.UBNR =
> TXMYTX00.AXBNR and TXUYUV00.UPLT=TXMYTX00.AXPLT) LEFT OUTER JOIN
> CSPSLS00 on TXUYUV00.UVT1 = CSPSLS00.SLSMAN
> WHERE
> bdg_view_GroupingByReceivableCustomer.ReportingGroup <> '3' AND
> TXUYUF01.VDAT >= 20050926 AND TXUYUF01.VDAT <= 20060924 AND
> TXUYUF01.VANR NOT LIKE 'ZZ%' AND
> bdg_view_GroupingByReceivableCustomer.ReportingGroup <> '5'
>
> It still runs and runs and takes a ton of CPU time. The Clustered
> Index s is taking 49% of the execution plan. When I run the query,
> I have to stop it immediately because it makes the CPU go to 90% and
> the system stays there.
> It didn't used to do this. I can't figure out why it does now.
>|||Brent
In addition to others I want to ask why do you use dates as intereger
datatype? Am I right?
> '3' AND TXUYUF01.VDAT >= 20050926 AND TXUYUF01.VDAT <= 20060924 AND
Or it is just wrong typing? Shoul be
> '3' AND TXUYUF01.VDAT >= '20050926' AND TXUYUF01.VDAT <= '20060924' AND
Run DBCC FREEPROCCACHE and your query and see what is going on
"Brent White" <bwhite@.badgersportswear.com> wrote in message
news:1147719197.120509.118220@.j33g2000cwa.googlegroups.com...
> need help ASAP on this.
> I had a stored procedure that pulls the "bookings" for our company
> (sales, if you will) for the past year. Before, it looked like this:
>
> SELECT UKDR, VANR, VMGS, VPR1, UDAT, VERA, UBLN,VMGL, ReportingGroup,
> UVT1, SLNAME, UVT2, AXTXT, USL1
> FROM (((bdg_view_GroupingByReceivableCustomer
INNER JOIN TXUYUV00 ON
> bdg_view_GroupingByReceivableCustomer.KKDR = TXUYUV00.UKDR) INNER JOIN
> TXUYUF01 ON TXUYUV00.UBLN = TXUYUF01.VBLN and
> TXUYUV00.UBLA=TXUYUF01.VBLA AND TXUYUV00.UBNR=TXUYUF01.VBNR AND
> TXUYUV00.UPLT = TXUYUF01.VPLT) INNER JOIN TXMYTX00 ON
> '#V'+TXUYUV00.UVT2 = TXMYTX00.AXANR AND TXUYUV00.UPLT=TXMYTX00.AXPLT)
> LEFT OUTER JOIN CSPSLS00 on TXUYUV00.UVT1 = CSPSLS00.SLSMAN
> WHERE
> UBLA<>'JB' AND bdg_view_GroupingByReceivableCustomer.ReportingGroup <>
> '3' AND TXUYUF01.VDAT >= 20050926 AND TXUYUF01.VDAT <= 20060924 AND
> TXUYUF01.VANR NOT LIKE 'ZZ%' AND
> bdg_view_GroupingByReceivableCustomer.ReportingGroup <> '5' AND
> TXUYUV00.USL1 NOT IN('PL','CO','FB','VW','VR','FC','CD','F
F','TR')
> It was running fine. It was a little slow, but not too bad.
> Fastforward to today. We added a new unit to our company, that now
> requires a second value for fields ending in "BNR" (VBNR, UBNR, etc.)
> Now the query looks like this:
>
> SELECT UKDR, VANR, VMGS, VPR1, UDAT, VERA, UBLN,VMGL, ReportingGroup,
> UVT1, SLNAME, UVT2, AXTXT, USL1
> FROM (((bdg_view_GroupingByReceivableCustomer
INNER JOIN TXUYUV00 ON
> bdg_view_GroupingByReceivableCustomer.KKDR = TXUYUV00.UKDR) INNER JOIN
> TXUYUF01 ON TXUYUV00.UBLN = TXUYUF01.VBLN and
> TXUYUV00.UBLA=TXUYUF01.VBLA AND TXUYUV00.UBNR=TXUYUF01.VBNR AND
> TXUYUV00.UPLT = TXUYUF01.VPLT) INNER JOIN TXMYTX00 ON
> '#V'+TXUYUV00.UVT2 = TXMYTX00.AXANR AND TXUYUV00.UBNR = TXMYTX00.AXBNR
> and TXUYUV00.UPLT=TXMYTX00.AXPLT) LEFT OUTER JOIN CSPSLS00 on
> TXUYUV00.UVT1 = CSPSLS00.SLSMAN
> WHERE
> UBLA<>'JB' AND bdg_view_GroupingByReceivableCustomer.ReportingGroup <>
> '3' AND TXUYUF01.VDAT >= 20050926 AND TXUYUF01.VDAT <= 20060924 AND
> TXUYUF01.VANR NOT LIKE 'ZZ%' AND
> bdg_view_GroupingByReceivableCustomer.ReportingGroup <> '5' AND
> TXUYUV00.USL1 NOT IN('PL','CO','FB','VW','VR','FC','CD','F
F','TR') and
> UBNR = '001'
>
> Problem? Though these tables have changed only modestly since this
> changeover (number of rows), the query takes 5 minutes to complete, and
> the CPU usage on a dual-processor server goes to 100%. I can't even
> bring up the task manager until the query completes. This wasn't a
> problem with the old procedure.
> Can someone help me figure this out and what I need to do?
>
>
> UPDATE: I have redone the query to look like this as per some other
> people in the SQL Server Central forum
>
> I have put a non-clustered index on the table TXUYUV00 involving UBLA,
> UBNR and USL1, and have redone the query to this:
> SELECT UKDR, VANR, VMGS, VPR1, UDAT, VERA, UBLN,VMGL, ReportingGroup,
> UVT1, SLNAME, UVT2, AXTXT, USL1
> FROM (((bdg_view_GroupingByReceivableCustomer
INNER JOIN TXUYUV00 ON
> bdg_view_GroupingByReceivableCustomer.KKDR = TXUYUV00.UKDR) INNER JOIN
> TXUYUF01 ON TXUYUV00.UBLN = TXUYUF01.VBLN and
> TXUYUV00.UBLA=TXUYUF01.VBLA AND TXUYUV00.UBNR=TXUYUF01.VBNR AND
> TXUYUV00.UPLT = TXUYUF01.VPLT AND TXUYUV00.UBNR = '001' and
> TXUYUV00.UBLA IN('SA','SO') and TXUYUV00.USL1 NOT
> IN('PL','CO','FB','VW','VR','FC','CD','F
F','TR') ) INNER JOIN TXMYTX00
> ON '#V'+TXUYUV00.UVT2 = TXMYTX00.AXANR AND TXUYUV00.UBNR =
> TXMYTX00.AXBNR and TXUYUV00.UPLT=TXMYTX00.AXPLT) LEFT OUTER JOIN
> CSPSLS00 on TXUYUV00.UVT1 = CSPSLS00.SLSMAN
> WHERE
> bdg_view_GroupingByReceivableCustomer.ReportingGroup <> '3' AND
> TXUYUF01.VDAT >= 20050926 AND TXUYUF01.VDAT <= 20060924 AND
> TXUYUF01.VANR NOT LIKE 'ZZ%' AND
> bdg_view_GroupingByReceivableCustomer.ReportingGroup <> '5'
>
> It still runs and runs and takes a ton of CPU time. The Clustered
> Index s is taking 49% of the execution plan. When I run the query,
> I have to stop it immediately because it makes the CPU go to 90% and
> the system stays there.
> It didn't used to do this. I can't figure out why it does now.
>|||I am using SQL Server 2000.
I didn't design this database; I'm tapping into the database set up by
our vendors for reporting purposes. All date columns are integer type,
using the yyyymmdd format (which, actually, really works great for
sorting because you don't have to have one program say '10/26/06' is a
string and before '10/27/05').
I apologize for my ignorance on some of your questions. First, how do
I even get the DDL for the tables in question? Also, I was under the
impression that FREEPROCCACHE would only work with ad hoc SQL
procedures, and I didn't think this would qualify as it is an actual
stored procedure. Will this tie up the system for others if I do this?
Colin:
I can't figure out what you're asking in #2:
2. what tables to does is column come from? Please modify the sql that
I've
attached below to include table alias's for all the columns. It'll
help
when it comes to re-jitting the code to make it more efficient.|||Hi Brent,
what I meant to say was, what table does each column come from? It is
important to know which tables each of the columns comes from, as this will
have a major impact on how the query can be modified.
Regards
Colin Dawson
www.cjdawson.com
"Brent White" <bwhite@.badgersportswear.com> wrote in message
news:1147784999.293403.286010@.g10g2000cwb.googlegroups.com...
>I am using SQL Server 2000.
> I didn't design this database; I'm tapping into the database set up by
> our vendors for reporting purposes. All date columns are integer type,
> using the yyyymmdd format (which, actually, really works great for
> sorting because you don't have to have one program say '10/26/06' is a
> string and before '10/27/05').
> I apologize for my ignorance on some of your questions. First, how do
> I even get the DDL for the tables in question? Also, I was under the
> impression that FREEPROCCACHE would only work with ad hoc SQL
> procedures, and I didn't think this would qualify as it is an actual
> stored procedure. Will this tie up the system for others if I do this?
> Colin:
> I can't figure out what you're asking in #2:
> 2. what tables to does is column come from? Please modify the sql that
> I've
> attached below to include table alias's for all the columns. It'll
> help
> when it comes to re-jitting the code to make it more efficient.
>|||On 16 May 2006 06:09:59 -0700, Brent White wrote:

>I am using SQL Server 2000.
>I didn't design this database; I'm tapping into the database set up by
>our vendors for reporting purposes. All date columns are integer type,
>using the yyyymmdd format (which, actually, really works great for
>sorting because you don't have to have one program say '10/26/06' is a
>string and before '10/27/05').
Hi Brent,
But the datetime would still be a better choice. Sorts as intended as
well, but has much better vallidation and enables easy date/time
calculations.

>I apologize for my ignorance on some of your questions. First, how do
>I even get the DDL for the tables in question?
See www.aspfaq.com/5006.

> Also, I was under the
>impression that FREEPROCCACHE would only work with ad hoc SQL
>procedures, and I didn't think this would qualify as it is an actual
>stored procedure. Will this tie up the system for others if I do this?
SQL Server stores execution plans for both stored procedures and ad-hoc
queries. FREEPROCCACHE will flush them all.
It will also reduce your popularity if you do this on a busy production
system. Better move to a test or dev server before playing around with
these options.
Hugo Kornelis, SQL Server MVP|||I have not tried the FREEPROCCACHE yet, but I did take part of the
query where it's pulling from TXUYUV00 and TXMYTX00 and made a view out
of the subset of TXMYTX00, and the row retrieval time is shorter than
ever (and I mean shorter than even before the problem surfaced), so for
now I think this is going to work. We made the person who uses the
report the most very happy.
As for the datetime, that's not my choice because it was done
externally. I have a function that converts it to an actual datetime
both in SQL Server and in Crystal Reports. Generally if I use a
parameter query in SQL Server for a stored procedure, I capture the
input dates as date time and convert just the parameters to the numeric
form and filter the recordsets that way. Saves a load off the SQL
Server having to convert the date fields for every record, and I then
capture the numeric date in Crystal Reports, converting the date to the
standard date/time.

Performance of SQL Server Express

For a small company with say 6 concurrent users, a database of say 10 Mbytes
and using only normal tables and indices (nothing fancy), will there be any
significant performance differences between SQL Server Workgroup and SQL
Server Express?
Thanks,
--
John Austin10MBs? I think you can safely say that Express won't be an issue and you
will see no differences in performance between Express and Workgroup on a db
that size.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"John Austin" <John.Austin@.nospam.nospam> wrote in message
news:72834DEC-E5FC-4787-A9AB-91CC3F0953B7@.microsoft.com...
> For a small company with say 6 concurrent users, a database of say 10
> Mbytes
> and using only normal tables and indices (nothing fancy), will there be
> any
> significant performance differences between SQL Server Workgroup and SQL
> Server Express?
> Thanks,
> --
> John Austin|||Hi John,
I do not think that there will be any significant performance differences
between SQL Server Workgroup and SQL Server Express Edition for your
scenario. However if you encountered any performance issue, you may refer
to the following article:
Troubleshooting Performance Problems in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
If you have any other questions or concerns, pelase feel free to let us
know. Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Thanks, Charles.
I have now been asked the same question about a simple 300 MB database with
10 to 15 users (virtually all access via primary keys). Do you know of any
guidance regarding the number of users and database size and recommended SQL
edition?
Thanks
--
John Austin
"Charles Wang[MSFT]" wrote:
> Hi John,
> I do not think that there will be any significant performance differences
> between SQL Server Workgroup and SQL Server Express Edition for your
> scenario. However if you encountered any performance issue, you may refer
> to the following article:
> Troubleshooting Performance Problems in SQL Server 2005
> http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
> If you have any other questions or concerns, pelase feel free to let us
> know. Have a nice day!
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> =====================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================>
>
>|||That is virtually impossible to tell based on so little data. Each app does
things differently and the data and schemas are different. Size of the DB
means little as to how many users it can support if it is properly tuned. If
most access is via PK's it makes the process very efficient, especially if
the PK is clustered to avoid BookMark Lookups. The only way to know for sure
is to test it under your exact conditions but you are probably safe.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"John Austin" <John.Austin@.nospam.nospam> wrote in message
news:B0F1C03C-0107-43A2-A5F4-9659DB45EF44@.microsoft.com...
> Thanks, Charles.
> I have now been asked the same question about a simple 300 MB database
> with
> 10 to 15 users (virtually all access via primary keys). Do you know of any
> guidance regarding the number of users and database size and recommended
> SQL
> edition?
> Thanks
> --
> John Austin
>
> "Charles Wang[MSFT]" wrote:
>> Hi John,
>> I do not think that there will be any significant performance differences
>> between SQL Server Workgroup and SQL Server Express Edition for your
>> scenario. However if you encountered any performance issue, you may refer
>> to the following article:
>> Troubleshooting Performance Problems in SQL Server 2005
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
>> If you have any other questions or concerns, pelase feel free to let us
>> know. Have a nice day!
>> Best regards,
>> Charles Wang
>> Microsoft Online Community Support
>> =====================================================>> When responding to posts, please "Reply to Group" via
>> your newsreader so that others may learn and benefit
>> from this issue.
>> ======================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> ======================================================>>
>>|||Andrew,
It is always tempting to over specify, just to be on the safe side. It is
helpful to talk to someone who has used the scenario in practice.
Thanks,
--
John Austin
"Andrew J. Kelly" wrote:
> That is virtually impossible to tell based on so little data. Each app does
> things differently and the data and schemas are different. Size of the DB
> means little as to how many users it can support if it is properly tuned. If
> most access is via PK's it makes the process very efficient, especially if
> the PK is clustered to avoid BookMark Lookups. The only way to know for sure
> is to test it under your exact conditions but you are probably safe.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "John Austin" <John.Austin@.nospam.nospam> wrote in message
> news:B0F1C03C-0107-43A2-A5F4-9659DB45EF44@.microsoft.com...
> > Thanks, Charles.
> >
> > I have now been asked the same question about a simple 300 MB database
> > with
> > 10 to 15 users (virtually all access via primary keys). Do you know of any
> > guidance regarding the number of users and database size and recommended
> > SQL
> > edition?
> >
> > Thanks
> >
> > --
> > John Austin
> >
> >
> > "Charles Wang[MSFT]" wrote:
> >
> >> Hi John,
> >> I do not think that there will be any significant performance differences
> >> between SQL Server Workgroup and SQL Server Express Edition for your
> >> scenario. However if you encountered any performance issue, you may refer
> >> to the following article:
> >> Troubleshooting Performance Problems in SQL Server 2005
> >> http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
> >>
> >> If you have any other questions or concerns, pelase feel free to let us
> >> know. Have a nice day!
> >>
> >> Best regards,
> >> Charles Wang
> >> Microsoft Online Community Support
> >> =====================================================> >> When responding to posts, please "Reply to Group" via
> >> your newsreader so that others may learn and benefit
> >> from this issue.
> >> ======================================================> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >> ======================================================> >>
> >>
> >>
> >>
> >>
>|||Hi Jason,
Thanks for your feedback.
Actually database size and the number of users are not the factors for
database performance. Assume that a very large database with a great many
of historical data, but the real operations are based on several tables
which only contain very few records. In other words, there is a job
scheduled to remove the old records to history tables which are only for
report sometimes. In this case, if every T-SQL statement can finish within
a very short time, it is possible that your SQL Server can handle hundreds
of connections in parallel. Of course the CPU licensing of your server need
to be able to support so many connections. I think that the real
performance here depends on your database design, server capability and
network bandwidth.
Appreciate your understanding that it is hard to say that there is any
performance issue with only knowing your database size and the number of
users. It is a good practice that you perform a test environment to see if
you encounter any performance issue before you put it into your production
environment. If you encounter any performance issue during your test
process, please feel free to post back. We are very glad to assist you
further.
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Thanks, Charles,
Express and Workgroup both have limits on processor count and memory usage.
The limit on database size should not be a problem and is easily
quantifiable. Presumably, if one is just reading data, CPU usage is not the
limiting factor, however I suspect memory usage may be. Is there a way of
tracking the memory usage of SQL Server standard in order to see if the
Express memory limits will impact performance?
Thanks,
--
John Austin
"Charles Wang[MSFT]" wrote:
> Hi Jason,
> Thanks for your feedback.
> Actually database size and the number of users are not the factors for
> database performance. Assume that a very large database with a great many
> of historical data, but the real operations are based on several tables
> which only contain very few records. In other words, there is a job
> scheduled to remove the old records to history tables which are only for
> report sometimes. In this case, if every T-SQL statement can finish within
> a very short time, it is possible that your SQL Server can handle hundreds
> of connections in parallel. Of course the CPU licensing of your server need
> to be able to support so many connections. I think that the real
> performance here depends on your database design, server capability and
> network bandwidth.
> Appreciate your understanding that it is hard to say that there is any
> performance issue with only knowing your database size and the number of
> users. It is a good practice that you perform a test environment to see if
> you encounter any performance issue before you put it into your production
> environment. If you encounter any performance issue during your test
> process, please feel free to post back. We are very glad to assist you
> further.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> =====================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================>|||One of the best indicators of lack of memory for things like queries is the
Page Life Expectancy counter in Perfmon found under the SQL Server Buffer
Manger object. The higher the counter the better it is. This is the
estimated number of seconds a typical data page will stay in cache before
being swapped out. Values under 100 start to indicate poor memory
utilization. This is true regardless of edition. But if your db size is
only 300MB then if you have the recommended 1GB on the machine this should
not be an issue.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"John Austin" <John.Austin@.nospam.nospam> wrote in message
news:561F12DF-DD1E-40EE-BA30-7BAA3E5F69D9@.microsoft.com...
> Thanks, Charles,
> Express and Workgroup both have limits on processor count and memory
> usage.
> The limit on database size should not be a problem and is easily
> quantifiable. Presumably, if one is just reading data, CPU usage is not
> the
> limiting factor, however I suspect memory usage may be. Is there a way of
> tracking the memory usage of SQL Server standard in order to see if the
> Express memory limits will impact performance?
> Thanks,
> --
> John Austin
>
> "Charles Wang[MSFT]" wrote:
>> Hi Jason,
>> Thanks for your feedback.
>> Actually database size and the number of users are not the factors for
>> database performance. Assume that a very large database with a great many
>> of historical data, but the real operations are based on several tables
>> which only contain very few records. In other words, there is a job
>> scheduled to remove the old records to history tables which are only for
>> report sometimes. In this case, if every T-SQL statement can finish
>> within
>> a very short time, it is possible that your SQL Server can handle
>> hundreds
>> of connections in parallel. Of course the CPU licensing of your server
>> need
>> to be able to support so many connections. I think that the real
>> performance here depends on your database design, server capability and
>> network bandwidth.
>> Appreciate your understanding that it is hard to say that there is any
>> performance issue with only knowing your database size and the number of
>> users. It is a good practice that you perform a test environment to see
>> if
>> you encounter any performance issue before you put it into your
>> production
>> environment. If you encounter any performance issue during your test
>> process, please feel free to post back. We are very glad to assist you
>> further.
>> Best regards,
>> Charles Wang
>> Microsoft Online Community Support
>> =====================================================>> When responding to posts, please "Reply to Group" via
>> your newsreader so that others may learn and benefit
>> from this issue.
>> ======================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> ======================================================>>|||Thanks Andrew, I will check that out. I am suggesting 4 GB for the server,
which may be over the top for this app, so we should be OK.
--
John Austin
"Andrew J. Kelly" wrote:
> One of the best indicators of lack of memory for things like queries is the
> Page Life Expectancy counter in Perfmon found under the SQL Server Buffer
> Manger object. The higher the counter the better it is. This is the
> estimated number of seconds a typical data page will stay in cache before
> being swapped out. Values under 100 start to indicate poor memory
> utilization. This is true regardless of edition. But if your db size is
> only 300MB then if you have the recommended 1GB on the machine this should
> not be an issue.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "John Austin" <John.Austin@.nospam.nospam> wrote in message
> news:561F12DF-DD1E-40EE-BA30-7BAA3E5F69D9@.microsoft.com...
> > Thanks, Charles,
> >
> > Express and Workgroup both have limits on processor count and memory
> > usage.
> > The limit on database size should not be a problem and is easily
> > quantifiable. Presumably, if one is just reading data, CPU usage is not
> > the
> > limiting factor, however I suspect memory usage may be. Is there a way of
> > tracking the memory usage of SQL Server standard in order to see if the
> > Express memory limits will impact performance?
> >
> > Thanks,
> > --
> > John Austin
> >
> >
> > "Charles Wang[MSFT]" wrote:
> >
> >> Hi Jason,
> >> Thanks for your feedback.
> >>
> >> Actually database size and the number of users are not the factors for
> >> database performance. Assume that a very large database with a great many
> >> of historical data, but the real operations are based on several tables
> >> which only contain very few records. In other words, there is a job
> >> scheduled to remove the old records to history tables which are only for
> >> report sometimes. In this case, if every T-SQL statement can finish
> >> within
> >> a very short time, it is possible that your SQL Server can handle
> >> hundreds
> >> of connections in parallel. Of course the CPU licensing of your server
> >> need
> >> to be able to support so many connections. I think that the real
> >> performance here depends on your database design, server capability and
> >> network bandwidth.
> >>
> >> Appreciate your understanding that it is hard to say that there is any
> >> performance issue with only knowing your database size and the number of
> >> users. It is a good practice that you perform a test environment to see
> >> if
> >> you encounter any performance issue before you put it into your
> >> production
> >> environment. If you encounter any performance issue during your test
> >> process, please feel free to post back. We are very glad to assist you
> >> further.
> >>
> >> Best regards,
> >> Charles Wang
> >> Microsoft Online Community Support
> >> =====================================================> >> When responding to posts, please "Reply to Group" via
> >> your newsreader so that others may learn and benefit
> >> from this issue.
> >> ======================================================> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >> ======================================================> >>
> >>
>|||Thanks for your feedback, John.
4GB is also the maximum database size that SQL Server 2005 Express can
support now. If your database size can be controlled under 4GB below, it
will be fine for your solution.
Just feel free to let us know if you encounter any issue in future. We are
very glad to work with you for further assistance.
Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Thank you for your kind assistance, Charles
--
John Austin
"Charles Wang[MSFT]" wrote:
> Thanks for your feedback, John.
> 4GB is also the maximum database size that SQL Server 2005 Express can
> support now. If your database size can be controlled under 4GB below, it
> will be fine for your solution.
> Just feel free to let us know if you encounter any issue in future. We are
> very glad to work with you for further assistance.
> Have a nice day!
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> =====================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================>
>
>