Query Across Multiple SQL Server Registrations

I have defined 2 SQL Server registrations in Ent Mgr. One is running locally and the other is located on another server. I want to be able to create a stored procedure in the local database that pulls data into a local table from the remote server.

I have wasted much time trying to define Linked Servers and Remote servers, and find the documentation all confusing and subsequently I have gotten nowhere (except frustrated). How do I configure the remote database so I can access it from a stored procedure in the local database?

Here is the code for creating the linked server. Run script against your local database. Hope you have access permissions on remote db. Also replace the IP address 999.999.999.999 with your remote system's IP address. You can query the tables on remote db with four part name.

Code Snippet

USE [master]
EXEC master.dbo.sp_addlinkedserver @.server = N'Lnk_RemoteDB', @.srvproduct=N'sqlserver', @.provider=N'SQLOLEDB',
@.datasrc = '999.999.999.999', -- IP Address
@.catalog=N'RemoteDB'
EXEC master.dbo.sp_serveroption @.server=N'Lnk_RemoteDB', @.optname=N'collation compatible', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'Lnk_RemoteDB', @.optname=N'data access', @.optvalue=N'true'
EXEC master.dbo.sp_serveroption @.server=N'Lnk_RemoteDB', @.optname=N'rpc', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'Lnk_RemoteDB', @.optname=N'rpc out', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'Lnk_RemoteDB', @.optname=N'connect timeout', @.optvalue=N'0'
EXEC master.dbo.sp_serveroption @.server=N'Lnk_RemoteDB', @.optname=N'collation name', @.optvalue=null
EXEC master.dbo.sp_serveroption @.server=N'Lnk_RemoteDB', @.optname=N'query timeout', @.optvalue=N'0'
EXEC master.dbo.sp_serveroption @.server=N'Lnk_RemoteDB', @.optname=N'use remote collation', @.optvalue=N'true'
EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = N'Lnk_RemoteDB', @.locallogin = NULL , @.useself = N'True'

|||

I ran the script and it created Lnk_RemoteDB. I want to access a table called Agents from a database named AgentDB, what's the correct syntax?

Thanks

|||

Sniegel wrote:

I ran the script and it created Lnk_RemoteDB. I want to access a table called Agents from a database named AgentDB, what's the correct syntax?

Thanks

select * from Lnk_RemoteDB.AgentDB.dbo.Agents|||I get an Authentication failed error. I tried switching the mode through EM and entering a username/password, but it didn't seem to work.

Query 2 tables - each in a different database

Is there any query code that will allow joining tables that are in different databases running on the same server? If yes, how is the connection to each handled?

That is no problem.

SELECT a.Field, b.Field
FROM dbo.myTable a
JOIN OtherDatabase.dbo.myTable b ON a.myKey = b.myKey

|||

The select code is clear but the connection to the two databases is not when using ADO. Is one connection object used somehow or are the databases linked?

|||

WeslyB is right.

But your user has to have select rights on the other database.

You don't have to handle the rights in ADO.

Regards

Query 2 table in different databases

Is there any query code that will allow joining tables that are in different databases running on the same server? If yes, how is the connection to each handled?

You need to use a 3-part name to reference the table from one database to another. The 3-part name consists of <database>.<schema>.<object>. The connection is only to the server and you can access any database to which you have permissions. You can either switch database context using USE <database> statement or use the 3-part name to reference objects.

Query : Running a job/step in a loop for ALL databases

Hi,

I've written a job to export user and database permissions for all
d/b's on a server. As you can see below, the T-SQL commands are the
same for each d/b. Can anyone assist with regard to re-writing this so
that any new d/b's added do not require ammending the job (loop)?

Thx,

GC.

use master
go
SELECT db_name()
EXEC sp_helpuser
EXEC sp_helprotect NULL, NULL, NULL, 'o s'
use msdb
go
SELECT db_name()
EXEC sp_helpuser
EXEC sp_helprotect NULL, NULL, NULL, 'o s'
use test1
go
SELECT db_name()
EXEC sp_helpuser
EXEC sp_helprotect NULL, NULL, NULL, 'o s'
use test2
go
SELECT db_name()
EXEC sp_helpuser
EXEC sp_helprotect NULL, NULL, NULL, 'o s'"Garry Clarke" <gclarke@.euro.banta.com> wrote in message
news:fed38413.0310240324.77f4ce60@.posting.google.c om...
> Hi,
> I've written a job to export user and database permissions for all
> d/b's on a server. As you can see below, the T-SQL commands are the
> same for each d/b. Can anyone assist with regard to re-writing this so
> that any new d/b's added do not require ammending the job (loop)?
> Thx,
> GC.
> use master
> go
> SELECT db_name()
> EXEC sp_helpuser
> EXEC sp_helprotect NULL, NULL, NULL, 'o s'
> use msdb
> go
> SELECT db_name()
> EXEC sp_helpuser
> EXEC sp_helprotect NULL, NULL, NULL, 'o s'
> use test1
> go
> SELECT db_name()
> EXEC sp_helpuser
> EXEC sp_helprotect NULL, NULL, NULL, 'o s'
> use test2
> go
> SELECT db_name()
> EXEC sp_helpuser
> EXEC sp_helprotect NULL, NULL, NULL, 'o s'

A cursor is one way to do this (cursors are usually a bad idea in
application code, but can be useful for admin scripts):

declare @.db sysname

declare cur_dbs cursor fast_forward
for select name from master..sysdatabases
order by name

open cur_dbs

fetch next from cur_dbs into @.db

while @.@.fetch_status = 0
begin
select @.db
exec('exec ' + @.db + '..sp_helpuser')
exec('exec ' +@.db + '..sp_helprotect NULL, NULL, NULL, ''os''')
fetch next from cur_dbs into @.db
end

close cur_dbs
deallocate cur_dbs

Simon

Query - Error: Expression caused an overflow

Hello!

Im running a application with a SQL CE database on a PDA (Qtek S200 and Qtek 2020i). Running the SQL CE version 3.1.

When querying the database with this question

SELECT Sum(Units) as sumUnits, tWorkReport.CreatedDate, tWorkReport.ModifiedDate FROM tWorkReport LEFT JOIN tWorkReportRow ON tWorkReport.WorkReportID = tWorkReportRow.WorkReportID WHERE 1=1 AND tWorkReport.PersonID = 160 GROUP BY tWorkReport.WorkReportID,GroupNr,ReportDate,PlantsPerUnit, tWorkReport.CreatedDate, tWorkReport.ModifiedDate ORDER BY ReportDate DESC

I get the error: Expression caused an overflow [,,, Name of function (if known),,] Native Error: 25901

A weird thing is that it has worked fine for a long time. After doing an update (adding records) to a table we got this error. The table that was updated has nothing to do with the SQL-query.

A temporary workaround for this was just write it "Sum(Convert(INT,Units) as Sumunits" but this means that Units with the format as decimal looses its decimals when converting to integer.

I came a across this error once before when doing development on the application - a workaround then was to write the sum like this " 1 - Sum(Units) - 1 as Sumunits" , this did not work in the latestest question.

I have tried breaking down the query to find where i get the error and it is the Sum function that gives this error.

Anyone else that has come across this error? Bug? Any workaround? The convert to int is just a temporary solution i want to use decimals!

Anyone? |||

Im still having this problem and havent got any solution yet. Reporting to Microsoft tomorrow.

Query - Error: Expression caused an overflow

Hello!

Im running a application with a SQL CE database on a PDA (Qtek S200 and Qtek 2020i). Running the SQL CE version 3.1.

When querying the database with this question

SELECT Sum(Units) as sumUnits, tWorkReport.CreatedDate, tWorkReport.ModifiedDate FROM tWorkReport LEFT JOIN tWorkReportRow ON tWorkReport.WorkReportID = tWorkReportRow.WorkReportID WHERE 1=1 AND tWorkReport.PersonID = 160 GROUP BY tWorkReport.WorkReportID,GroupNr,ReportDate,PlantsPerUnit, tWorkReport.CreatedDate, tWorkReport.ModifiedDate ORDER BY ReportDate DESC

I get the error: Expression caused an overflow [,,, Name of function (if known),,] Native Error: 25901

A weird thing is that it has worked fine for a long time. After doing an update (adding records) to a table we got this error. The table that was updated has nothing to do with the SQL-query.

A temporary workaround for this was just write it "Sum(Convert(INT,Units) as Sumunits" but this means that Units with the format as decimal looses its decimals when converting to integer.

I came a across this error once before when doing development on the application - a workaround then was to write the sum like this " 1 - Sum(Units) - 1 as Sumunits" , this did not work in the latestest question.

I have tried breaking down the query to find where i get the error and it is the Sum function that gives this error.

Anyone else that has come across this error? Bug? Any workaround? The convert to int is just a temporary solution i want to use decimals!

Anyone? |||

Im still having this problem and havent got any solution yet. Reporting to Microsoft tomorrow.

Query -- 11 seconds in SSMS but times out after 30 minutes in

We're having a weird problem -- a particular report will time out
after 30 minutes running in Reporting Services. However, if we grab
the query from Profiler and run it in SSMS, it runs in about 11
seconds. The profiler trace shows an error of 2 - Abort.
The query itself is found below -- I won't include DDL because each of
the items in the FROM clause is a view that is very long.
Any explanation as to why the query would take so long when running
from reporting services?
Our environment -- SQL 2005 Standard x64, SP2 (9.00.3054.00 / Build
3790). Running on Windows Server 2003 Enterprise x64 SP2.
There are some things I'd like to clean up after seeing it, but
nonetheless it still runs pretty quick in SSMS.
SELECT
e.COMPANY, b.PLAN_NO, b.DESC_, c.CLIENT, c.CLIENT_NO
,CASE eb.option_ WHEN 1 THEN b.desc_1 WHEN 2 THEN b.desc_2
WHEN 3 THEN b.desc_3
WHEN 4 THEN b.desc_4 WHEN 5 THEN b.desc_5 WHEN 6 THEN
b.desc_6
WHEN 7 THEN b.desc_7 ELSE b.desc_8 END AS Plan_Option
, e.SHORT_NAME, e.SSN
, SUM(bck.COST)
, e.STATUS
,case when e.terminated ='1753-01-01 00:00:00.000' then ' '
else LEFT( CONVERT(varchar,e.terminated, 120), 10) end as term_date
FROM
ahr_custom.sls.nocorp_ckreg ck
inner join ahr_custom.sls.nocorp_prckreg p ON p.ck_no = ck.ck_no AND p.ck_style = ck.ck_style AND p.company = ck.company
inner join ahr_custom.sls.nocorp_beckreg bck ON p.ck_no = bck.ck_no AND p.ck_style = bck.ck_style AND p.company = bck.company
inner join ahr_custom.sls.nocorp_beneplan b on b.company = bck.company and b.plan_no = bck.plan_no
inner join ahr_custom.sls.nocorp_employee e on e.company = ck.company and e.emp_no = p.emp_no
inner join ahr_custom.sls.nocorp_empbene eb on bck.company = eb.company and e.emp_no = eb.emp_no and eb.plan_no = bck.plan_no
inner join ahr_custom.sls.nocorp_clients c on ck.company = c.company and c.client_no = e.client_no1
WHERE
bck.PLAN_NO IN (20676)
AND p.COMPANY IN ('OK','CA','C2','CO','TX')
and Left(CONVERT(varchar,ck.ck_date,120),10)
between cast(year(getdate()-20) as varchar(4)) +'-'+ (right( '0'
+cast(month(getdate()-20) as varchar(2)),2))+ '-01'
and LEFT( CONVERT(varchar,
ahr_custom.dbo.lastdayofmonth(getdate()-20) , 120), 10)
GROUP BY
e.COMPANY, b.PLAN_NO, b.DESC_, c.CLIENT, c.CLIENT_NO
,CASE eb.option_ WHEN 1 THEN b.desc_1 WHEN 2 THEN b.desc_2 WHEN 3
THEN b.desc_3
WHEN 4 THEN b.desc_4 WHEN 5 THEN b.desc_5 WHEN 6 THEN b.desc_6
WHEN 7 THEN b.desc_7 ELSE b.desc_8 END
, e.SHORT_NAME, e.SSN
,e.STATUS
,case when e.terminated ='1753-01-01 00:00:00.000' then ' ' else
LEFT( CONVERT(varchar,e.terminated, 120), 10) endHow many rows of data is returned?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<stephen.lee.moore@.gmail.com> wrote in message
news:ac49df7d-bd90-4f6d-b7c8-44012e8e9bb3@.w56g2000hsf.googlegroups.com...
> We're having a weird problem -- a particular report will time out
> after 30 minutes running in Reporting Services. However, if we grab
> the query from Profiler and run it in SSMS, it runs in about 11
> seconds. The profiler trace shows an error of 2 - Abort.
> The query itself is found below -- I won't include DDL because each of
> the items in the FROM clause is a view that is very long.
> Any explanation as to why the query would take so long when running
> from reporting services?
> Our environment -- SQL 2005 Standard x64, SP2 (9.00.3054.00 / Build
> 3790). Running on Windows Server 2003 Enterprise x64 SP2.
> There are some things I'd like to clean up after seeing it, but
> nonetheless it still runs pretty quick in SSMS.
> SELECT
> e.COMPANY, b.PLAN_NO, b.DESC_, c.CLIENT, c.CLIENT_NO
> ,CASE eb.option_ WHEN 1 THEN b.desc_1 WHEN 2 THEN b.desc_2
> WHEN 3 THEN b.desc_3
> WHEN 4 THEN b.desc_4 WHEN 5 THEN b.desc_5 WHEN 6 THEN
> b.desc_6
> WHEN 7 THEN b.desc_7 ELSE b.desc_8 END AS Plan_Option
> , e.SHORT_NAME, e.SSN
> , SUM(bck.COST)
> , e.STATUS
> ,case when e.terminated ='1753-01-01 00:00:00.000' then ' '
> else LEFT( CONVERT(varchar,e.terminated, 120), 10) end as term_date
> FROM
> ahr_custom.sls.nocorp_ckreg ck
> inner join ahr_custom.sls.nocorp_prckreg p ON p.ck_no => ck.ck_no AND p.ck_style = ck.ck_style AND p.company = ck.company
> inner join ahr_custom.sls.nocorp_beckreg bck ON p.ck_no => bck.ck_no AND p.ck_style = bck.ck_style AND p.company = bck.company
> inner join ahr_custom.sls.nocorp_beneplan b on b.company => bck.company and b.plan_no = bck.plan_no
> inner join ahr_custom.sls.nocorp_employee e on e.company => ck.company and e.emp_no = p.emp_no
> inner join ahr_custom.sls.nocorp_empbene eb on bck.company => eb.company and e.emp_no = eb.emp_no and eb.plan_no = bck.plan_no
> inner join ahr_custom.sls.nocorp_clients c on ck.company => c.company and c.client_no = e.client_no1
> WHERE
> bck.PLAN_NO IN (20676)
> AND p.COMPANY IN ('OK','CA','C2','CO','TX')
> and Left(CONVERT(varchar,ck.ck_date,120),10)
> between cast(year(getdate()-20) as varchar(4)) +'-'+ (right( '0'
> +cast(month(getdate()-20) as varchar(2)),2))+ '-01'
> and LEFT( CONVERT(varchar,
> ahr_custom.dbo.lastdayofmonth(getdate()-20) , 120), 10)
> GROUP BY
> e.COMPANY, b.PLAN_NO, b.DESC_, c.CLIENT, c.CLIENT_NO
> ,CASE eb.option_ WHEN 1 THEN b.desc_1 WHEN 2 THEN b.desc_2 WHEN 3
> THEN b.desc_3
> WHEN 4 THEN b.desc_4 WHEN 5 THEN b.desc_5 WHEN 6 THEN b.desc_6
> WHEN 7 THEN b.desc_7 ELSE b.desc_8 END
> , e.SHORT_NAME, e.SSN
> ,e.STATUS
> ,case when e.terminated ='1753-01-01 00:00:00.000' then ' ' else
> LEFT( CONVERT(varchar,e.terminated, 120), 10) end|||On Dec 4, 11:07 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> How many rows of data is returned?
502 rows|||OK, number of rows is not a problem. My guess is that you are having an
issue with the query plan being different.
First, can you put this in a stored procedure and then call that from both
places and see if that makes a difference?
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<stephen.lee.moore@.gmail.com> wrote in message
news:90230c5c-f7c7-4592-8b44-83ce3a381729@.s12g2000prg.googlegroups.com...
> On Dec 4, 11:07 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
> wrote:
>> How many rows of data is returned?
> 502 rows|||> First, can you put this in a stored procedure and then call that from both
> places and see if that makes a difference?
Wow -- good suggestion! That seemed to fix it.
Any ideas why in the world would a different query plan be generated
from Reporting Services than from SSMS? It seems like either A)
Profiler isn't giving me the right query that's being executed via
Reporting Services or B) different query plans are being generated for
the same SQL statement depending on the calling application. Neither
of those make any sense to me.
Thanks again for your help.|||I have seen this with other people but with stored procedures not SQL. If a
stored procedure acts differently then you can add a With Recompile to fix
the problem. I really don't understand how the same SQL can act different
but that is what I thought was happening to you.
Bruce
<stephen.lee.moore@.gmail.com> wrote in message
news:2cd5adad-6a86-414b-9840-2b3b05e58c31@.j44g2000hsj.googlegroups.com...
>> First, can you put this in a stored procedure and then call that from
>> both
>> places and see if that makes a difference?
> Wow -- good suggestion! That seemed to fix it.
> Any ideas why in the world would a different query plan be generated
> from Reporting Services than from SSMS? It seems like either A)
> Profiler isn't giving me the right query that's being executed via
> Reporting Services or B) different query plans are being generated for
> the same SQL statement depending on the calling application. Neither
> of those make any sense to me.
> Thanks again for your help.

Query

Hi
I need this statement to print to the same log.txt when running xp_cmdshell
PRINT 'Upgrading.... ' + @.DataBaseName
How do I fit it into this command?
set @.command = 'osql -Usa -Padmin -S jaco_ -d ' + @.DataBaseName + ' -i
c:\temp\Complete(7-9).sql' + ' -n ' + ' >> c:\temp\log.txt'
exec master..xp_cmdshell @.command
Thanks
JacoHi
Use
PRINT 'Upgrading.... ' + DB_NAME()
instead.
John
"Jaco" wrote:

> Hi
> I need this statement to print to the same log.txt when running xp_cmdshel
l
> PRINT 'Upgrading.... ' + @.DataBaseName
> How do I fit it into this command?
> set @.command = 'osql -Usa -Padmin -S jaco_ -d ' + @.DataBaseName + ' -i
> c:\temp\Complete(7-9).sql' + ' -n ' + ' >> c:\temp\log.txt'
> exec master..xp_cmdshell @.command
> Thanks
> Jaco
>|||On Tue, 20 Sep 2005 03:16:05 -0700, Jaco wrote:

>Hi
>I need this statement to print to the same log.txt when running xp_cmdshell
>PRINT 'Upgrading.... ' + @.DataBaseName
>How do I fit it into this command?
>set @.command = 'osql -Usa -Padmin -S jaco_ -d ' + @.DataBaseName + ' -i
>c:\temp\Complete(7-9).sql' + ' -n ' + ' >> c:\temp\log.txt'
>exec master..xp_cmdshell @.command
>Thanks
>Jaco
Hi Jaco,
If you can change the contents of c:\temp\Complete(7-9).sql, then add
this command at the beginning:
PRINT 'Upgrading.... ' + DB_NAME()
If you can't change c:\temp\Complete(7-9).sql, then use this:
set @.command = 'echo Upgrading.... ' + @.DataBaseName
+ ' >> c:\temp\log.txt'
exec master..xp_cmdshell @.command
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql

Queries with different results...

Hi,
I am getting a difference of '1' when I am running the queries which I am listing below...
1)
select count(distinct(I.order_ID)) as completedTotal
from T_INVOICES I, T_ORDERS O,T_STATUS S
where
O.order_Id = I.order_ID

and I.feeDate >= {d '2002-08-01'} and I.feeDate < {d '2002-09-01'}
AND client_ID NOT IN(14,693) AND NOT EXISTS(SELECT Order_ID FROM T_STATUS WHERE
order_id = O.order_ID AND StatusType_ID = 7)
__________________________________________________ __________
2)
select count(distinct(I.order_ID)) as completedTotal
from T_INVOICES I
LEFT OUTER JOIN T_ORDERS AS O ON (O.order_Id = I.order_ID)
LEFT OUTER JOIN T_STATUS AS S ON (s.Order_ID = O.order_id)

WHERE I.feeDate >= {d '2002-08-01'} and I.feeDate < {d '2002-09-01'}
AND client_ID NOT IN(14,693) AND NOT EXISTS(SELECT Order_ID FROM T_STATUS WHERE order_id = O.order_ID AND StatusType_ID = 7)

can anyone help inresolving this.

thanksIn your first query, table S is not joined to table O, resulting in a cross-join.

blindman|||could you say how to optimize the second query?|||Well, I would script it like this for clarity, but this is just my style (it looks better once you paste it without the line-wrapping):

select count(distinct(I.order_ID)) as completedTotal
from T_INVOICES
LEFT OUTER JOIN T_ORDERS on T_ORDERS.order_Id = T_INVOICES.order_ID
LEFT OUTER JOIN T_STATUS on T_STATUS.Order_ID = T_ORDERS.order_id
WHERE T_INVOICES.feeDate '2002-08-01' and T_INVOICES.feeDate < '2002-09-01'
AND client_ID NOT IN(14,693)
AND NOT EXISTS (SELECT Order_ID FROM T_STATUS WHERE order_id = T_ORDERS.order_ID AND StatusType_ID = 7)

I've never liked using short aliases for tables because I've never thought the time saved in typing was worth the cost in readability.

You can optimize your query by indexing the join columns and criteria columns: order_id, feeDate, client_ID, and StatusType, but it may not be necessary to index all of them to get good performance.

Make sure you understand how your NOT EXISTS clause is going to affect your output. It will exclude all invoice and order records where there is a status recrod = 7, even if there are additional associated status records that do not = 7. If you just want to exclude status records = 7 from your output, put your criteria in the join:

select count(distinct(I.order_ID)) as completedTotal
from T_INVOICES
LEFT OUTER JOIN T_ORDERS on T_ORDERS.order_Id = T_INVOICES.order_ID
LEFT OUTER JOIN T_STATUS on T_STATUS.Order_ID = T_ORDERS.order_id T_STATUS.StatusType_ID <> 7
WHERE T_INVOICES.feeDate '2002-08-01' and T_INVOICES.feeDate < '2002-09-01'
AND client_ID NOT IN(14,693)

Also, I don't know how you have your relationships set up, but by the way you have three tables joined on order_id I suspect you are violating database normalization guidelines. (Unless the orders table has a one-to-many relationship with both Invoices and Status tables.)

blindman|||Hi,
I am using the query,

select count(distinct(T_INVOICES.order_ID)) as completedTotal
from T_INVOICES
LEFT OUTER JOIN T_ORDERS on T_ORDERS.order_Id = T_INVOICES.order_ID
LEFT OUTER JOIN T_STATUS on T_STATUS.Order_ID = T_ORDERS.order_id and T_STATUS.StatusType_ID <> 7
where
T_INVOICES.feeDate >= {d '2002-08-01'} and T_INVOICES.feeDate < {d '2002-09-01'}
AND client_ID NOT IN(14,693)
__________________________________________________ ___

but i am getting the number so high of the actual. Actually I have to get '29' instead I am getting '46'. Can you explain it.

thanks,
siva|||Instead of:
select count(distinct(T_INVOICES.order_ID)) as completedTotal

try this:
select distinct T_INVOICES.order_ID

...in order to see what data is actually being counted. I'm not sure your count(distinct( syntax is going to work the way you are expecting it to.

Also, try this:
select count(distinct T_INVOICES.order_ID) as completedTotal

The extra parentheses you have aren't necessary and may be affected your results.

blindman|||still iam getting the same result...|||So how many rows were returned by "select distinct T_INVOICES.order_ID"? Were any of them duplicates? Were any there that weren't supposed to be there?

Your problem my be due to, or compounded by, the relationships established between your tables. What is the architecture of these three tables:

Invoices -> Orders -> Status?

or

Invoices <- Orders -> Status?

You may need to try building your query from scratch again, starting with a simple select from Invoices and then adding joins and criteria as you verify that you are getting the data you expect. Your query design has some subtle joins and criteria, but I can't tell if these are required to get the results you want, or if they are just coding issues to be cleaned up.

blindmansql

queries running very slow on a particular table

I have a web application that hits this database 24/7. I have an orders table
and any query that I run on this table is very slow. A simple select query
also runs very slow. This was working fine until this morning. All the other
tables work fine. I ran dbcc showcontig on then ran dbcc dbreindex. Still no
good. Please advise.Have you looked at what the query plan shows for the query on the orders
table? It could be the statistics need to be updated, however the dbcc
dbreindex should have handled that. You may need to run a profiler to
see if the problem is with the table access or perhaps temp tables.
Shahryar
Beginner wrote:
>I have a web application that hits this database 24/7. I have an orders table
>and any query that I run on this table is very slow. A simple select query
>also runs very slow. This was working fine until this morning. All the other
>tables work fine. I ran dbcc showcontig on then ran dbcc dbreindex. Still no
>good. Please advise.
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohibited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.|||Any blocking going on?
run sp_who2 and look for the BlkBy column
http://sqlservercode.blogspot.com/

queries running very slow on a particular table

I have a web application that hits this database 24/7. I have an orders tabl
e
and any query that I run on this table is very slow. A simple select query
also runs very slow. This was working fine until this morning. All the other
tables work fine. I ran dbcc showcontig on then ran dbcc dbreindex. Still no
good. Please advise.Have you looked at what the query plan shows for the query on the orders
table? It could be the statistics need to be updated, however the dbcc
dbreindex should have handled that. You may need to run a profiler to
see if the problem is with the table access or perhaps temp tables.
Shahryar
Beginner wrote:

>I have a web application that hits this database 24/7. I have an orders tab
le
>and any query that I run on this table is very slow. A simple select query
>also runs very slow. This was working fine until this morning. All the othe
r
>tables work fine. I ran dbcc showcontig on then ran dbcc dbreindex. Still n
o
>good. Please advise.
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is
legally privileged. The information is solely for the use of the intended
recipient(s); any disclosure, copying, distribution, or other use of this in
formation is strictly prohi
bited. If you have received this e-mail in error, please notify the sender
by return e-mail and delete this message. Thank you.|||Any blocking going on?
run sp_who2 and look for the BlkBy column
http://sqlservercode.blogspot.com/sql

queries running very slow on a particular table

I have a web application that hits this database 24/7. I have an orders table
and any query that I run on this table is very slow. A simple select query
also runs very slow. This was working fine until this morning. All the other
tables work fine. I ran dbcc showcontig on then ran dbcc dbreindex. Still no
good. Please advise.
Have you looked at what the query plan shows for the query on the orders
table? It could be the statistics need to be updated, however the dbcc
dbreindex should have handled that. You may need to run a profiler to
see if the problem is with the table access or perhaps temp tables.
Shahryar
Beginner wrote:

>I have a web application that hits this database 24/7. I have an orders table
>and any query that I run on this table is very slow. A simple select query
>also runs very slow. This was working fine until this morning. All the other
>tables work fine. I ran dbcc showcontig on then ran dbcc dbreindex. Still no
>good. Please advise.
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohi
bited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.
|||Any blocking going on?
run sp_who2 and look for the BlkBy column
http://sqlservercode.blogspot.com/

Queries returning Multiple instances of the same record

I am running SQL 2005 and have created a simple database for interfacing with Visual Web Dev 2005 and Visual Studio 2005. I noticed that my applications where returning multiple instances of the same records in the queries. I went back to the SQL server and created a query, and sure enough, I'm getting each record returned 3 times. Where do I start to resolve this issue?DId you have the records already duplicated in the database tables ? Or did you just made a mistake in your join, defining the wrong joined keys. THe best thing would be to provide some information like DDL and some sample data.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Jens,

My DB and query are much simpler than what you are imagining:

The DB Structure is:

<MemberID, Int,> - Primary Key Autoincrement

<FirstName, nvarchar(30),>

<LastName, nvarchar(30),>

<Salutation, nvarchar(20),>

<MemberType, nvarchar(20),>

<IsNeighbor, tinyint,>

<Title, nvarchar(30),>

<Address, nvarchar(60),>

<Address2, nvarchar(60),>

<City, nvarchar(30),>

<State, nvarchar(2),>

<Zip, nvarchar(9),>

<Phone, nvarchar(10),>

<Email, nvarchar(50),>

<DateJoined, datetime,>

<ExpirationDate, datetime,>

<SubMemberTo, int,>

<Fax, nvarchar(10),>

<Cellphone, nvarchar(10),>

The SELECT query is:

SELECT [MemberID]

,[FirstName]

,[LastName]

,[Salutation]

,[MemberType]

,[IsNeighbor]

,[Title]

,[Address]

,[Address2]

,[City]

,[State]

,[Zip]

,[Phone]

,[Email]

,[DateJoined]

,[ExpirationDate]

,[SubMemberTo]

,[Fax]

,[Cellphone]

FROM [FriendsSQL].[dbo].[Members]

WHERE FirstName = 'ANNE' and LastName = 'REIS'

Without the WHERE clause the query returns the entire DB without duplication, however, when the WHERE clause is included the output is:

13 ANNE REIS Anne Reis & Owen Boy Full Member 0 Environmental Coordinator

XXXX E KENILWORTH PL NULL MILWAUKEE WI 53202 4147370000

XXXXX@.PLANET-SAVE.COM 2005-11-01 00:00:00.000 NULL NULL NULL NULL

13 ANNE REIS Anne Reis & Owen Boy Full Member 0 Environmental Coordinator

XXXX E KENILWORTH PL NULL MILWAUKEE WI 53202 4147370000

XXXXX@.PLANET-SAVE.COM 2005-11-01 00:00:00.000 NULL NULL NULL NULL

13 ANNE REIS Anne Reis & Owen Boy Full Member 0 Environmental Coordinator

XXXX E KENILWORTH PL NULL MILWAUKEE WI 53202 4147370000

XXXXX@.PLANET-SAVE.COM 2005-11-01 00:00:00.000 NULL NULL NULL NULL

Notice that the single record is returned 3 times.

|||DOH! You were right. The records were duplicated. Apparently using the SET Insert Unique ON and not having the Primary Key set allowed the duplications. I've cleaned up the mess and I'll try not to shoot off any more toes. Sorry for the bother. I should have caught that one.

Queries in parallel

I have some users running an application against a SQL 2k enterprise
database with 4 CPU's. When multiple users execute the same task in the
application at the same time, it appears from a duration standpoint that the
process is running serially against the database, where user #2's task
finishes after User #1, and User #3 finishes after User #2.
The configuration of the database has the following values:
name minimum maximum config_value
run_value
-- -- -- -- --
affinity mask -2147483648 2147483647 0 0
allow updates 0 1 0 0
awe enabled 0 1 1 1
c2 audit mode 0 1 0 0
cost threshold for parallelism 0 32767 5 5
Cross DB Ownership Chaining 0 1 0 0
cursor threshold -1 2147483647 -1 -1
default full-text language 0 2147483647 1033 1033
default language 0 9999 0 0
fill factor (%) 0 100 0 0
index create memory (KB) 704 2147483647 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max degree of parallelism 0 32 0 0
max server memory (MB) 4 2147483647 6079 6079
max text repl size (B) 0 2147483647 65536
65536
max worker threads 32 32767 255 255
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 6079 6079
nested triggers 0 1 1 1
network packet size (B) 512 32767 4096 4096
open objects 0 2147483647 0 0
priority boost 0 1 0 0
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote login timeout (s) 0 2147483647 20 20
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 0 0
scan for startup procs 0 1 0 0
set working set size 0 1 0 0
show advanced options 0 1 1 1
two digit year cutoff 1753 9999 2049 2049
user connections 0 32767 0 0
user options 0 32767 0 0
Is there anyway to prove that the queries are running in parallel rather than
serially?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200601/1
Robert R via droptable.com wrote:
> I have some users running an application against a SQL 2k enterprise
> database with 4 CPU's. When multiple users execute the same task in
> the application at the same time, it appears from a duration
> standpoint that the process is running serially against the database,
> where user #2's task finishes after User #1, and User #3 finishes
> after User #2.
> <snip>
Sounds like you are having blocking issues. That is, one query is blocking
other queries and because of that they are executing in a serial fashion.
Check the execution plans of the queries in the "task" to make sure they are
properly using indexes and make sure you avoid long running transactions.
Feel free to post the table DDL, indexes, queries in question, and the
execution plans for each if you have any questions. You can check much of
this information from Profiler. Use the SQL:BatchCompleted and RPC:Completed
events for a high-level performance overview. If you want more detail add
SQL:StmtCompleted and SP:StmtCompleted events. Look over the cpu, duration,
and reads columns.
David Gugick
Quest Software
|||This is a sample of one of the long running queries. Keep in mind the sample
I have included here is only about a quarter of the entire statement. Care to
explain how this might block other queries so that a newbie like me can
understand?
(
SELECT
ol.KY_OPINION_CD_ID AS KY_OPINION_CD_ID,
min(SKU_NOTE_2) AS SKU_NOTE_2,
min(SKU_NOTE) AS SKU_NOTE,
SKU_01 AS CURR_GROUP_CD,
SKU_01 AS CURR_GROUP_TEXT,
'SKU_01' AS GROUP_ORDER_CD,
COUNT(*) AS NUM_CHILDREN,
'2559946|SKU' AS AGGR_NAME,
2626210 AS AGGR_ID,
Min(cs.KY_SKU_ID) AS KY_SKU_ID,
Min(cs.KY_CUST_ID) as KY_CUST_ID,
min(CUST_CD) AS CUST_CD,
min(SKU_CD) AS SKU_CD,
min(BUS_UNIT_CD) AS BUS_UNIT_CD,
min(CUST_01) as CUST_01,
min(CUST_02) as CUST_02,
min(CUST_03) as CUST_03,
min(CUST_04) as CUST_04,
min(CUST_05) as CUST_05,
min(CUST_06) as CUST_06,
min(CUST_07) as CUST_07,
min(CUST_08) as CUST_08,
min(SKU_01) as SKU_01,
min(SKU_02) as SKU_02,
min(SKU_03) as SKU_03,
min(SKU_04) as SKU_04,
min(SKU_05) as SKU_05,
min(SKU_06) as SKU_06,
min(SKU_07) as SKU_07,
min(SKU_08) as SKU_08,
min(SKU_09) as SKU_09,
min(SKU_10) as SKU_10,
min(SKU_11) as SKU_11,
min(SKU_12) as SKU_12,
min(SKU_13) as SKU_13,
min(SKU_14) as SKU_14,
min(SKU_15) as SKU_15,
min(SKU_16) as SKU_16,
min(SKU_17) as SKU_17,
min(SKU_18) as SKU_18,
min(SKU_19) as SKU_19,
min(SKU_20) as SKU_20,
min(SKU_21) as SKU_21,
min(SKU_22) as SKU_22,
min(SKU_23) as SKU_23,
min(SKU_24) as SKU_24,
min(SKU_25) as SKU_25,
min(SKU_26) as SKU_26,
min(SKU_27) as SKU_27,
min(SKU_28) as SKU_28,
min(SKU_29) as SKU_29,
min(SKU_30) as SKU_30,
min(SKU_31) as SKU_31,
min(SKU_32) as SKU_32,
min(SKU_33) as SKU_33,
min(SKU_34) as SKU_34,
avg(SKU_AMT_01) as SKU_AMT_01,
avg(SKU_AMT_02) as SKU_AMT_02,
avg(SKU_AMT_03) as SKU_AMT_03,
min(SKU_AMT_04) as SKU_AMT_04,
avg(SKU_AMT_05) as SKU_AMT_05,
min(SKU_DATE_01) as SKU_DATE_01,
min(SKU_DATE_02) as SKU_DATE_02,
min(SKU_DATE_03) as SKU_DATE_03,
max(CUST_SKU_01) as CUST_SKU_01,
min(CUST_SKU_02) as CUST_SKU_02,
min(CUST_SKU_03) as CUST_SKU_03,
min(CUST_SKU_04) as CUST_SKU_04,
max(CUST_SKU_05) as CUST_SKU_05,
min(CUST_SKU_06) as CUST_SKU_06,
min(CUST_SKU_07) as CUST_SKU_07,
min(CUST_SKU_08) as CUST_SKU_08,
min(CUST_SKU_09) as CUST_SKU_09,
min(CUST_SKU_10) as CUST_SKU_10,
min(CUST_SKU_11) as CUST_SKU_11,
min(CUST_SKU_12) as CUST_SKU_12,
min(CUST_SKU_13) as CUST_SKU_13,
min(CUST_SKU_14) as CUST_SKU_14,
min(CUST_SKU_15) as CUST_SKU_15,
max(CUST_SKU_16) as CUST_SKU_16,
max(CUST_SKU_17) as CUST_SKU_17,
max(CUST_SKU_18) as CUST_SKU_18,
max(CUST_SKU_19) as CUST_SKU_19,
max(CUST_SKU_20) as CUST_SKU_20,
max(CUST_SKU_21) as CUST_SKU_21,
max(CUST_SKU_22) as CUST_SKU_22,
max(CUST_SKU_23) as CUST_SKU_23,
max(CUST_SKU_24) as CUST_SKU_24,
max(CUST_SKU_25) as CUST_SKU_25,
max(CUST_SKU_26) as CUST_SKU_26,
max(CUST_SKU_27) as CUST_SKU_27,
max(CUST_SKU_30) as CUST_SKU_30,
avg(CUST_SKU_AMT_01) as CUST_SKU_AMT_01,
avg(CUST_SKU_AMT_02) as CUST_SKU_AMT_02,
avg(CUST_SKU_AMT_03) as CUST_SKU_AMT_03,
avg(CUST_SKU_AMT_04) as CUST_SKU_AMT_04,
avg(CUST_SKU_AMT_05) as CUST_SKU_AMT_05,
avg(CUST_SKU_AMT_06) as CUST_SKU_AMT_06,
avg(CUST_SKU_AMT_07) as CUST_SKU_AMT_07,
avg(CUST_SKU_AMT_08) as CUST_SKU_AMT_08,
sum(CUST_SKU_AMT_09) as CUST_SKU_AMT_09,
sum(CUST_SKU_AMT_10) as CUST_SKU_AMT_10,
sum(CUST_SKU_AMT_11) as CUST_SKU_AMT_11,
sum(CUST_SKU_AMT_11*p1.OPINION_QTY_01) as CUST_SKU_AMT_12,
'UOM' AS MIN_UM,
'UOM' AS MAX_UM,
(SELECT COUNT(*) from tblNotes n with(rowlock)
WHERE n.KY_SKU_ID = min(cs.KY_SKU_ID)
and NOTE_TYPE IS NOT NULL) AS HAS_NOTE,
0 AS DIRTY_OP,
SPACE(100) AS UPDATE_FLDS,
(SUM(ol.OPINION_QTY_N12)) as OPINION_QTY_N12,
(SUM(ol.OPINION_QTY_N11)) as OPINION_QTY_N11,
(SUM(ol.OPINION_QTY_N10)) as OPINION_QTY_N10,
(SUM(ol.OPINION_QTY_N09)) as OPINION_QTY_N09,
(SUM(ol.OPINION_QTY_N08)) as OPINION_QTY_N08,
(SUM(ol.OPINION_QTY_N07)) as OPINION_QTY_N07,
(SUM(ol.OPINION_QTY_N06)) as OPINION_QTY_N06,
(SUM(ol.OPINION_QTY_N05)) as OPINION_QTY_N05,
(SUM(ol.OPINION_QTY_N04)) as OPINION_QTY_N04,
(SUM(ol.OPINION_QTY_N03)) as OPINION_QTY_N03,
(SUM(ol.OPINION_QTY_N02)) as OPINION_QTY_N02,
(SUM(ol.OPINION_QTY_N01)) as OPINION_QTY_N01,
(SUM(ol.OPINION_QTY_01)) as OPINION_QTY_01,
(SUM(ol.OPINION_QTY_02)) as OPINION_QTY_02,
(SUM(ol.OPINION_QTY_03)) as OPINION_QTY_03,
(SUM(ol.OPINION_QTY_04)) as OPINION_QTY_04,
(SUM(ol.OPINION_QTY_05)) as OPINION_QTY_05,
(SUM(ol.OPINION_QTY_06)) as OPINION_QTY_06,
(SUM(ol.OPINION_QTY_07)) as OPINION_QTY_07,
(SUM(ol.OPINION_QTY_08)) as OPINION_QTY_08,
(SUM(ol.OPINION_QTY_09)) as OPINION_QTY_09,
(SUM(ol.OPINION_QTY_10)) as OPINION_QTY_10,
(SUM(ol.OPINION_QTY_11)) as OPINION_QTY_11,
(SUM(ol.OPINION_QTY_12)) as OPINION_QTY_12,
(SUM(ol.OPINION_QTY_13)) as OPINION_QTY_13,
(SUM(ol.OPINION_QTY_14)) as OPINION_QTY_14,
(SUM(ol.OPINION_QTY_15)) as OPINION_QTY_15,
(SUM(ol.OPINION_QTY_16)) as OPINION_QTY_16,
(SUM(ol.OPINION_QTY_17)) as OPINION_QTY_17,
(SUM(ol.OPINION_QTY_18)) as OPINION_QTY_18,
(SUM(ol.OPINION_QTY_19)) as OPINION_QTY_19,
(SUM(ol.OPINION_QTY_20)) as OPINION_QTY_20,
(SUM(ol.OPINION_QTY_21)) as OPINION_QTY_21,
(SUM(ol.OPINION_QTY_22)) as OPINION_QTY_22,
(SUM(ol.OPINION_QTY_23)) as OPINION_QTY_23,
(SUM(ol.OPINION_QTY_24)) as OPINION_QTY_24,
(SUM(ol.OPINION_QTY_25)) as OPINION_QTY_25,
(SUM(ol.OPINION_QTY_26)) as OPINION_QTY_26,
(SUM(ol.OPINION_QTY_27)) as OPINION_QTY_27,
(SUM(ol.OPINION_QTY_28)) as OPINION_QTY_28,
(SUM(ol.OPINION_QTY_29)) as OPINION_QTY_29,
(SUM(ol.OPINION_QTY_30)) as OPINION_QTY_30,
(SUM(ol.OPINION_QTY_31)) as OPINION_QTY_31,
(SUM(ol.OPINION_QTY_32)) as OPINION_QTY_32,
(SUM(ol.OPINION_QTY_33)) as OPINION_QTY_33,
(SUM(ol.OPINION_QTY_34)) as OPINION_QTY_34,
(SUM(ol.OPINION_QTY_35)) as OPINION_QTY_35,
(SUM(ol.OPINION_QTY_36)) as OPINION_QTY_36,
(SUM(ol.OPINION_AMT_N24)) AS OPINION_AMT_N24,
(SUM(ol.OPINION_AMT_N23)) AS OPINION_AMT_N23,
(SUM(ol.OPINION_AMT_N22)) AS OPINION_AMT_N22,
(SUM(ol.OPINION_AMT_N21)) AS OPINION_AMT_N21,
(SUM(ol.OPINION_AMT_N20)) AS OPINION_AMT_N20,
(SUM(ol.OPINION_AMT_N19)) AS OPINION_AMT_N19,
(SUM(ol.OPINION_AMT_N18)) AS OPINION_AMT_N18,
(SUM(ol.OPINION_AMT_N17)) AS OPINION_AMT_N17,
(SUM(ol.OPINION_AMT_N16)) AS OPINION_AMT_N16,
(SUM(ol.OPINION_AMT_N15)) AS OPINION_AMT_N15,
(SUM(ol.OPINION_AMT_N14)) AS OPINION_AMT_N14,
(SUM(ol.OPINION_AMT_N13)) AS OPINION_AMT_N13,
(SUM(ol.OPINION_AMT_N12)) AS OPINION_AMT_N12,
(SUM(ol.OPINION_AMT_N11)) AS OPINION_AMT_N11,
(SUM(ol.OPINION_AMT_N10)) AS OPINION_AMT_N10,
(SUM(ol.OPINION_AMT_N09)) AS OPINION_AMT_N09,
(SUM(ol.OPINION_AMT_N08)) AS OPINION_AMT_N08,
(SUM(ol.OPINION_AMT_N07)) AS OPINION_AMT_N07,
(SUM(ol.OPINION_AMT_N06)) AS OPINION_AMT_N06,
(SUM(ol.OPINION_AMT_N05)) AS OPINION_AMT_N05,
(SUM(ol.OPINION_AMT_N04)) AS OPINION_AMT_N04,
(SUM(ol.OPINION_AMT_N03)) AS OPINION_AMT_N03,
(SUM(ol.OPINION_AMT_N02)) AS OPINION_AMT_N02,
(SUM(ol.OPINION_AMT_N01)) AS OPINION_AMT_N01,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_01*p3.OPINION_QTY_01)
else 0 end) AS OPINION_AMT_01,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_02*p3.OPINION_QTY_02)
else 0 end) AS OPINION_AMT_02,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_03*p3.OPINION_QTY_03)
else 0 end) AS OPINION_AMT_03,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_04*p3.OPINION_QTY_04)
else 0 end) AS OPINION_AMT_04,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_05*p3.OPINION_QTY_05)
else 0 end) AS OPINION_AMT_05,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_06*p3.OPINION_QTY_06)
else 0 end) AS OPINION_AMT_06,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_07*p3.OPINION_QTY_07)
else 0 end) AS OPINION_AMT_07,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_08*p3.OPINION_QTY_08)
else 0 end) AS OPINION_AMT_08,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_09*p3.OPINION_QTY_09)
else 0 end) AS OPINION_AMT_09,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_10*p3.OPINION_QTY_10)
else 0 end) AS OPINION_AMT_10,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_11*p3.OPINION_QTY_11)
else 0 end) AS OPINION_AMT_11,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_12*p3.OPINION_QTY_12)
else 0 end) AS OPINION_AMT_12,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_13*p3.OPINION_QTY_13)
else 0 end) AS OPINION_AMT_13,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_14*p3.OPINION_QTY_14)
else 0 end) AS OPINION_AMT_14,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_15*p3.OPINION_QTY_15)
else 0 end) AS OPINION_AMT_15,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_16*p3.OPINION_QTY_16)
else 0 end) AS OPINION_AMT_16,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_17*p3.OPINION_QTY_17)
else 0 end) AS OPINION_AMT_17,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_18*p3.OPINION_QTY_18)
else 0 end) AS OPINION_AMT_18,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_19*p3.OPINION_QTY_19)
else 0 end) AS OPINION_AMT_19,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_20*p3.OPINION_QTY_20)
else 0 end) AS OPINION_AMT_20,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_21*p3.OPINION_QTY_21)
else 0 end) AS OPINION_AMT_21,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_22*p3.OPINION_QTY_22)
else 0 end) AS OPINION_AMT_22,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_23*p3.OPINION_QTY_23)
else 0 end) AS OPINION_AMT_23,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_24*p3.OPINION_QTY_24)
else 0 end) AS OPINION_AMT_24,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_25*p3.OPINION_QTY_25)
else 0 end) AS OPINION_AMT_25,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_26*p3.OPINION_QTY_26)
else 0 end) AS OPINION_AMT_26,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_27*p3.OPINION_QTY_27)
else 0 end) AS OPINION_AMT_27,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_28*p3.OPINION_QTY_28)
else 0 end) AS OPINION_AMT_28,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_29*p3.OPINION_QTY_29)
else 0 end) AS OPINION_AMT_29,
case ...
from ...
where ...
David Gugick wrote:
>[quoted text clipped - 4 lines]
>Sounds like you are having blocking issues. That is, one query is blocking
>other queries and because of that they are executing in a serial fashion.
>Check the execution plans of the queries in the "task" to make sure they are
>properly using indexes and make sure you avoid long running transactions.
>Feel free to post the table DDL, indexes, queries in question, and the
>execution plans for each if you have any questions. You can check much of
>this information from Profiler. Use the SQL:BatchCompleted and RPC:Completed
>events for a high-level performance overview. If you want more detail add
>SQL:StmtCompleted and SP:StmtCompleted events. Look over the cpu, duration,
>and reads columns.
>
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200601/1
|||Robert R via droptable.com wrote:
> This is a sample of one of the long running queries. Keep in mind the
> sample I have included here is only about a quarter of the entire
> statement. Care to explain how this might block other queries so that
> a newbie like me can understand?
> <snip>
Where did that SQL statement come from? It looks as though your table is
denormalized. I see a SKU_1 through SKU_34 that appear to be columns in
the table. If that's true, table design issues aside, that's an
extremely large and complex query that could very well be accessing the
entire table each time it's executed and / or causing extremely high IO
load on your disk subsystem and / or causing high CPU. You are also
returning a SPACE(100) as a column which is a little strange.
Denormalized tables cause large row sizes also very low row density
which means a lot of page reads. Excessive page reads mean a lot of
physical IO which is slow. A lot of aggregations means higher CPU
utilization. All of these items can translate in to slow executing
queries, long transaction times, extended locks that cause blocking,
high disk IO, hign CPU, etc.
So, first we need to know if your table really looks like what I see in
the query. If so, the answers here will likely point to the table design
as the primary problem. It would be impossible for anyone here to
recommend a solution for the query itself without knowing exactly what
the tables look like, what indexes are in place, what you're hoping to
return from the query, and a complete query with parameters/bind
variables.
Sorry I can't be more help. THe best thing to do is take a step back and
make a new post with all the relevant information.
David Gugick
Quest Software
www.quest.com
sql

Queries in parallel

I have some users running an application against a SQL 2k enterprise
database with 4 CPU's. When multiple users execute the same task in the
application at the same time, it appears from a duration standpoint that the
process is running serially against the database, where user #2's task
finishes after User #1, and User #3 finishes after User #2.
The configuration of the database has the following values:
name minimum maximum config_value
run_value
-- -- -- -- --
--
affinity mask -2147483648 2147483647 0 0
allow updates 0 1 0 0
awe enabled 0 1 1 1
c2 audit mode 0 1 0 0
cost threshold for parallelism 0 32767 5 5
Cross DB Ownership Chaining 0 1 0 0
cursor threshold -1 2147483647 -1 -1
default full-text language 0 2147483647 1033 1033
default language 0 9999 0 0
fill factor (%) 0 100 0 0
index create memory (KB) 704 2147483647 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max degree of parallelism 0 32 0 0
max server memory (MB) 4 2147483647 6079 6079
max text repl size (B) 0 2147483647 65536
65536
max worker threads 32 32767 255 255
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 6079 6079
nested triggers 0 1 1 1
network packet size (B) 512 32767 4096 4096
open objects 0 2147483647 0 0
priority boost 0 1 0 0
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote login timeout (s) 0 2147483647 20 20
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 0 0
scan for startup procs 0 1 0 0
set working set size 0 1 0 0
show advanced options 0 1 1 1
two digit year cutoff 1753 9999 2049 2049
user connections 0 32767 0 0
user options 0 32767 0 0
Is there anyway to prove that the queries are running in parallel rather than
serially?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200601/1Robert R via SQLMonster.com wrote:
> I have some users running an application against a SQL 2k enterprise
> database with 4 CPU's. When multiple users execute the same task in
> the application at the same time, it appears from a duration
> standpoint that the process is running serially against the database,
> where user #2's task finishes after User #1, and User #3 finishes
> after User #2.
> <snip>
Sounds like you are having blocking issues. That is, one query is blocking
other queries and because of that they are executing in a serial fashion.
Check the execution plans of the queries in the "task" to make sure they are
properly using indexes and make sure you avoid long running transactions.
Feel free to post the table DDL, indexes, queries in question, and the
execution plans for each if you have any questions. You can check much of
this information from Profiler. Use the SQL:BatchCompleted and RPC:Completed
events for a high-level performance overview. If you want more detail add
SQL:StmtCompleted and SP:StmtCompleted events. Look over the cpu, duration,
and reads columns.
David Gugick
Quest Software|||This is a sample of one of the long running queries. Keep in mind the sample
I have included here is only about a quarter of the entire statement. Care to
explain how this might block other queries so that a newbie like me can
understand?
(
SELECT
ol.KY_OPINION_CD_ID AS KY_OPINION_CD_ID,
min(SKU_NOTE_2) AS SKU_NOTE_2,
min(SKU_NOTE) AS SKU_NOTE,
SKU_01 AS CURR_GROUP_CD,
SKU_01 AS CURR_GROUP_TEXT,
'SKU_01' AS GROUP_ORDER_CD,
COUNT(*) AS NUM_CHILDREN,
'2559946|SKU' AS AGGR_NAME,
2626210 AS AGGR_ID,
Min(cs.KY_SKU_ID) AS KY_SKU_ID,
Min(cs.KY_CUST_ID) as KY_CUST_ID,
min(CUST_CD) AS CUST_CD,
min(SKU_CD) AS SKU_CD,
min(BUS_UNIT_CD) AS BUS_UNIT_CD,
min(CUST_01) as CUST_01,
min(CUST_02) as CUST_02,
min(CUST_03) as CUST_03,
min(CUST_04) as CUST_04,
min(CUST_05) as CUST_05,
min(CUST_06) as CUST_06,
min(CUST_07) as CUST_07,
min(CUST_08) as CUST_08,
min(SKU_01) as SKU_01,
min(SKU_02) as SKU_02,
min(SKU_03) as SKU_03,
min(SKU_04) as SKU_04,
min(SKU_05) as SKU_05,
min(SKU_06) as SKU_06,
min(SKU_07) as SKU_07,
min(SKU_08) as SKU_08,
min(SKU_09) as SKU_09,
min(SKU_10) as SKU_10,
min(SKU_11) as SKU_11,
min(SKU_12) as SKU_12,
min(SKU_13) as SKU_13,
min(SKU_14) as SKU_14,
min(SKU_15) as SKU_15,
min(SKU_16) as SKU_16,
min(SKU_17) as SKU_17,
min(SKU_18) as SKU_18,
min(SKU_19) as SKU_19,
min(SKU_20) as SKU_20,
min(SKU_21) as SKU_21,
min(SKU_22) as SKU_22,
min(SKU_23) as SKU_23,
min(SKU_24) as SKU_24,
min(SKU_25) as SKU_25,
min(SKU_26) as SKU_26,
min(SKU_27) as SKU_27,
min(SKU_28) as SKU_28,
min(SKU_29) as SKU_29,
min(SKU_30) as SKU_30,
min(SKU_31) as SKU_31,
min(SKU_32) as SKU_32,
min(SKU_33) as SKU_33,
min(SKU_34) as SKU_34,
avg(SKU_AMT_01) as SKU_AMT_01,
avg(SKU_AMT_02) as SKU_AMT_02,
avg(SKU_AMT_03) as SKU_AMT_03,
min(SKU_AMT_04) as SKU_AMT_04,
avg(SKU_AMT_05) as SKU_AMT_05,
min(SKU_DATE_01) as SKU_DATE_01,
min(SKU_DATE_02) as SKU_DATE_02,
min(SKU_DATE_03) as SKU_DATE_03,
max(CUST_SKU_01) as CUST_SKU_01,
min(CUST_SKU_02) as CUST_SKU_02,
min(CUST_SKU_03) as CUST_SKU_03,
min(CUST_SKU_04) as CUST_SKU_04,
max(CUST_SKU_05) as CUST_SKU_05,
min(CUST_SKU_06) as CUST_SKU_06,
min(CUST_SKU_07) as CUST_SKU_07,
min(CUST_SKU_08) as CUST_SKU_08,
min(CUST_SKU_09) as CUST_SKU_09,
min(CUST_SKU_10) as CUST_SKU_10,
min(CUST_SKU_11) as CUST_SKU_11,
min(CUST_SKU_12) as CUST_SKU_12,
min(CUST_SKU_13) as CUST_SKU_13,
min(CUST_SKU_14) as CUST_SKU_14,
min(CUST_SKU_15) as CUST_SKU_15,
max(CUST_SKU_16) as CUST_SKU_16,
max(CUST_SKU_17) as CUST_SKU_17,
max(CUST_SKU_18) as CUST_SKU_18,
max(CUST_SKU_19) as CUST_SKU_19,
max(CUST_SKU_20) as CUST_SKU_20,
max(CUST_SKU_21) as CUST_SKU_21,
max(CUST_SKU_22) as CUST_SKU_22,
max(CUST_SKU_23) as CUST_SKU_23,
max(CUST_SKU_24) as CUST_SKU_24,
max(CUST_SKU_25) as CUST_SKU_25,
max(CUST_SKU_26) as CUST_SKU_26,
max(CUST_SKU_27) as CUST_SKU_27,
max(CUST_SKU_30) as CUST_SKU_30,
avg(CUST_SKU_AMT_01) as CUST_SKU_AMT_01,
avg(CUST_SKU_AMT_02) as CUST_SKU_AMT_02,
avg(CUST_SKU_AMT_03) as CUST_SKU_AMT_03,
avg(CUST_SKU_AMT_04) as CUST_SKU_AMT_04,
avg(CUST_SKU_AMT_05) as CUST_SKU_AMT_05,
avg(CUST_SKU_AMT_06) as CUST_SKU_AMT_06,
avg(CUST_SKU_AMT_07) as CUST_SKU_AMT_07,
avg(CUST_SKU_AMT_08) as CUST_SKU_AMT_08,
sum(CUST_SKU_AMT_09) as CUST_SKU_AMT_09,
sum(CUST_SKU_AMT_10) as CUST_SKU_AMT_10,
sum(CUST_SKU_AMT_11) as CUST_SKU_AMT_11,
sum(CUST_SKU_AMT_11*p1.OPINION_QTY_01) as CUST_SKU_AMT_12,
'UOM' AS MIN_UM,
'UOM' AS MAX_UM,
(SELECT COUNT(*) from tblNotes n with(rowlock)
WHERE n.KY_SKU_ID = min(cs.KY_SKU_ID)
and NOTE_TYPE IS NOT NULL) AS HAS_NOTE,
0 AS DIRTY_OP,
SPACE(100) AS UPDATE_FLDS,
(SUM(ol.OPINION_QTY_N12)) as OPINION_QTY_N12,
(SUM(ol.OPINION_QTY_N11)) as OPINION_QTY_N11,
(SUM(ol.OPINION_QTY_N10)) as OPINION_QTY_N10,
(SUM(ol.OPINION_QTY_N09)) as OPINION_QTY_N09,
(SUM(ol.OPINION_QTY_N08)) as OPINION_QTY_N08,
(SUM(ol.OPINION_QTY_N07)) as OPINION_QTY_N07,
(SUM(ol.OPINION_QTY_N06)) as OPINION_QTY_N06,
(SUM(ol.OPINION_QTY_N05)) as OPINION_QTY_N05,
(SUM(ol.OPINION_QTY_N04)) as OPINION_QTY_N04,
(SUM(ol.OPINION_QTY_N03)) as OPINION_QTY_N03,
(SUM(ol.OPINION_QTY_N02)) as OPINION_QTY_N02,
(SUM(ol.OPINION_QTY_N01)) as OPINION_QTY_N01,
(SUM(ol.OPINION_QTY_01)) as OPINION_QTY_01,
(SUM(ol.OPINION_QTY_02)) as OPINION_QTY_02,
(SUM(ol.OPINION_QTY_03)) as OPINION_QTY_03,
(SUM(ol.OPINION_QTY_04)) as OPINION_QTY_04,
(SUM(ol.OPINION_QTY_05)) as OPINION_QTY_05,
(SUM(ol.OPINION_QTY_06)) as OPINION_QTY_06,
(SUM(ol.OPINION_QTY_07)) as OPINION_QTY_07,
(SUM(ol.OPINION_QTY_08)) as OPINION_QTY_08,
(SUM(ol.OPINION_QTY_09)) as OPINION_QTY_09,
(SUM(ol.OPINION_QTY_10)) as OPINION_QTY_10,
(SUM(ol.OPINION_QTY_11)) as OPINION_QTY_11,
(SUM(ol.OPINION_QTY_12)) as OPINION_QTY_12,
(SUM(ol.OPINION_QTY_13)) as OPINION_QTY_13,
(SUM(ol.OPINION_QTY_14)) as OPINION_QTY_14,
(SUM(ol.OPINION_QTY_15)) as OPINION_QTY_15,
(SUM(ol.OPINION_QTY_16)) as OPINION_QTY_16,
(SUM(ol.OPINION_QTY_17)) as OPINION_QTY_17,
(SUM(ol.OPINION_QTY_18)) as OPINION_QTY_18,
(SUM(ol.OPINION_QTY_19)) as OPINION_QTY_19,
(SUM(ol.OPINION_QTY_20)) as OPINION_QTY_20,
(SUM(ol.OPINION_QTY_21)) as OPINION_QTY_21,
(SUM(ol.OPINION_QTY_22)) as OPINION_QTY_22,
(SUM(ol.OPINION_QTY_23)) as OPINION_QTY_23,
(SUM(ol.OPINION_QTY_24)) as OPINION_QTY_24,
(SUM(ol.OPINION_QTY_25)) as OPINION_QTY_25,
(SUM(ol.OPINION_QTY_26)) as OPINION_QTY_26,
(SUM(ol.OPINION_QTY_27)) as OPINION_QTY_27,
(SUM(ol.OPINION_QTY_28)) as OPINION_QTY_28,
(SUM(ol.OPINION_QTY_29)) as OPINION_QTY_29,
(SUM(ol.OPINION_QTY_30)) as OPINION_QTY_30,
(SUM(ol.OPINION_QTY_31)) as OPINION_QTY_31,
(SUM(ol.OPINION_QTY_32)) as OPINION_QTY_32,
(SUM(ol.OPINION_QTY_33)) as OPINION_QTY_33,
(SUM(ol.OPINION_QTY_34)) as OPINION_QTY_34,
(SUM(ol.OPINION_QTY_35)) as OPINION_QTY_35,
(SUM(ol.OPINION_QTY_36)) as OPINION_QTY_36,
(SUM(ol.OPINION_AMT_N24)) AS OPINION_AMT_N24,
(SUM(ol.OPINION_AMT_N23)) AS OPINION_AMT_N23,
(SUM(ol.OPINION_AMT_N22)) AS OPINION_AMT_N22,
(SUM(ol.OPINION_AMT_N21)) AS OPINION_AMT_N21,
(SUM(ol.OPINION_AMT_N20)) AS OPINION_AMT_N20,
(SUM(ol.OPINION_AMT_N19)) AS OPINION_AMT_N19,
(SUM(ol.OPINION_AMT_N18)) AS OPINION_AMT_N18,
(SUM(ol.OPINION_AMT_N17)) AS OPINION_AMT_N17,
(SUM(ol.OPINION_AMT_N16)) AS OPINION_AMT_N16,
(SUM(ol.OPINION_AMT_N15)) AS OPINION_AMT_N15,
(SUM(ol.OPINION_AMT_N14)) AS OPINION_AMT_N14,
(SUM(ol.OPINION_AMT_N13)) AS OPINION_AMT_N13,
(SUM(ol.OPINION_AMT_N12)) AS OPINION_AMT_N12,
(SUM(ol.OPINION_AMT_N11)) AS OPINION_AMT_N11,
(SUM(ol.OPINION_AMT_N10)) AS OPINION_AMT_N10,
(SUM(ol.OPINION_AMT_N09)) AS OPINION_AMT_N09,
(SUM(ol.OPINION_AMT_N08)) AS OPINION_AMT_N08,
(SUM(ol.OPINION_AMT_N07)) AS OPINION_AMT_N07,
(SUM(ol.OPINION_AMT_N06)) AS OPINION_AMT_N06,
(SUM(ol.OPINION_AMT_N05)) AS OPINION_AMT_N05,
(SUM(ol.OPINION_AMT_N04)) AS OPINION_AMT_N04,
(SUM(ol.OPINION_AMT_N03)) AS OPINION_AMT_N03,
(SUM(ol.OPINION_AMT_N02)) AS OPINION_AMT_N02,
(SUM(ol.OPINION_AMT_N01)) AS OPINION_AMT_N01,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_01*p3.OPINION_QTY_01)
else 0 end) AS OPINION_AMT_01,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_02*p3.OPINION_QTY_02)
else 0 end) AS OPINION_AMT_02,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_03*p3.OPINION_QTY_03)
else 0 end) AS OPINION_AMT_03,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_04*p3.OPINION_QTY_04)
else 0 end) AS OPINION_AMT_04,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_05*p3.OPINION_QTY_05)
else 0 end) AS OPINION_AMT_05,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_06*p3.OPINION_QTY_06)
else 0 end) AS OPINION_AMT_06,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_07*p3.OPINION_QTY_07)
else 0 end) AS OPINION_AMT_07,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_08*p3.OPINION_QTY_08)
else 0 end) AS OPINION_AMT_08,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_09*p3.OPINION_QTY_09)
else 0 end) AS OPINION_AMT_09,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_10*p3.OPINION_QTY_10)
else 0 end) AS OPINION_AMT_10,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_11*p3.OPINION_QTY_11)
else 0 end) AS OPINION_AMT_11,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_12*p3.OPINION_QTY_12)
else 0 end) AS OPINION_AMT_12,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_13*p3.OPINION_QTY_13)
else 0 end) AS OPINION_AMT_13,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_14*p3.OPINION_QTY_14)
else 0 end) AS OPINION_AMT_14,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_15*p3.OPINION_QTY_15)
else 0 end) AS OPINION_AMT_15,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_16*p3.OPINION_QTY_16)
else 0 end) AS OPINION_AMT_16,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_17*p3.OPINION_QTY_17)
else 0 end) AS OPINION_AMT_17,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_18*p3.OPINION_QTY_18)
else 0 end) AS OPINION_AMT_18,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_19*p3.OPINION_QTY_19)
else 0 end) AS OPINION_AMT_19,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_20*p3.OPINION_QTY_20)
else 0 end) AS OPINION_AMT_20,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_21*p3.OPINION_QTY_21)
else 0 end) AS OPINION_AMT_21,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_22*p3.OPINION_QTY_22)
else 0 end) AS OPINION_AMT_22,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_23*p3.OPINION_QTY_23)
else 0 end) AS OPINION_AMT_23,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_24*p3.OPINION_QTY_24)
else 0 end) AS OPINION_AMT_24,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_25*p3.OPINION_QTY_25)
else 0 end) AS OPINION_AMT_25,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_26*p3.OPINION_QTY_26)
else 0 end) AS OPINION_AMT_26,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_27*p3.OPINION_QTY_27)
else 0 end) AS OPINION_AMT_27,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_28*p3.OPINION_QTY_28)
else 0 end) AS OPINION_AMT_28,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_29*p3.OPINION_QTY_29)
else 0 end) AS OPINION_AMT_29,
case ...
from ...
where ...
David Gugick wrote:
>> I have some users running an application against a SQL 2k enterprise
>> database with 4 CPU's. When multiple users execute the same task in
>[quoted text clipped - 4 lines]
>> <snip>
>Sounds like you are having blocking issues. That is, one query is blocking
>other queries and because of that they are executing in a serial fashion.
>Check the execution plans of the queries in the "task" to make sure they are
>properly using indexes and make sure you avoid long running transactions.
>Feel free to post the table DDL, indexes, queries in question, and the
>execution plans for each if you have any questions. You can check much of
>this information from Profiler. Use the SQL:BatchCompleted and RPC:Completed
>events for a high-level performance overview. If you want more detail add
>SQL:StmtCompleted and SP:StmtCompleted events. Look over the cpu, duration,
>and reads columns.
>
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200601/1|||Robert R via SQLMonster.com wrote:
> This is a sample of one of the long running queries. Keep in mind the
> sample I have included here is only about a quarter of the entire
> statement. Care to explain how this might block other queries so that
> a newbie like me can understand?
> <snip>
Where did that SQL statement come from? It looks as though your table is
denormalized. I see a SKU_1 through SKU_34 that appear to be columns in
the table. If that's true, table design issues aside, that's an
extremely large and complex query that could very well be accessing the
entire table each time it's executed and / or causing extremely high IO
load on your disk subsystem and / or causing high CPU. You are also
returning a SPACE(100) as a column which is a little strange.
Denormalized tables cause large row sizes also very low row density
which means a lot of page reads. Excessive page reads mean a lot of
physical IO which is slow. A lot of aggregations means higher CPU
utilization. All of these items can translate in to slow executing
queries, long transaction times, extended locks that cause blocking,
high disk IO, hign CPU, etc.
So, first we need to know if your table really looks like what I see in
the query. If so, the answers here will likely point to the table design
as the primary problem. It would be impossible for anyone here to
recommend a solution for the query itself without knowing exactly what
the tables look like, what indexes are in place, what you're hoping to
return from the query, and a complete query with parameters/bind
variables.
Sorry I can't be more help. THe best thing to do is take a step back and
make a new post with all the relevant information.
David Gugick
Quest Software
www.quest.com

Queries in parallel

I have some users running an application against a SQL 2k enterprise
database with 4 CPU's. When multiple users execute the same task in the
application at the same time, it appears from a duration standpoint that the
process is running serially against the database, where user #2's task
finishes after User #1, and User #3 finishes after User #2.
The configuration of the database has the following values:
name minimum maximum config_value
run_value
-- -- -- -- --
--
--
affinity mask -2147483648 2147483647 0 0
allow updates 0 1 0 0
awe enabled 0 1 1 1
c2 audit mode 0 1 0 0
cost threshold for parallelism 0 32767 5 5
Cross DB Ownership Chaining 0 1 0 0
cursor threshold -1 2147483647 -1 -1
default full-text language 0 2147483647 1033 103
3
default language 0 9999 0 0
fill factor (%) 0 100 0 0
index create memory (KB) 704 2147483647 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max degree of parallelism 0 32 0 0
max server memory (MB) 4 2147483647 6079 607
9
max text repl size (B) 0 2147483647 65536
65536
max worker threads 32 32767 255 255
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 102
4
min server memory (MB) 0 2147483647 6079 607
9
nested triggers 0 1 1 1
network packet size (B) 512 32767 4096 409
6
open objects 0 2147483647 0 0
priority boost 0 1 0 0
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote login timeout (s) 0 2147483647 20 20
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 0 0
scan for startup procs 0 1 0 0
set working set size 0 1 0 0
show advanced options 0 1 1 1
two digit year cutoff 1753 9999 2049 204
9
user connections 0 32767 0 0
user options 0 32767 0 0
Is there anyway to prove that the queries are running in parallel rather tha
n
serially?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200601/1Robert R via droptable.com wrote:
> I have some users running an application against a SQL 2k enterprise
> database with 4 CPU's. When multiple users execute the same task in
> the application at the same time, it appears from a duration
> standpoint that the process is running serially against the database,
> where user #2's task finishes after User #1, and User #3 finishes
> after User #2.
> <snip>
Sounds like you are having blocking issues. That is, one query is blocking
other queries and because of that they are executing in a serial fashion.
Check the execution plans of the queries in the "task" to make sure they are
properly using indexes and make sure you avoid long running transactions.
Feel free to post the table DDL, indexes, queries in question, and the
execution plans for each if you have any questions. You can check much of
this information from Profiler. Use the SQL:BatchCompleted and RPC:Completed
events for a high-level performance overview. If you want more detail add
SQL:StmtCompleted and SP:StmtCompleted events. Look over the cpu, duration,
and reads columns.
David Gugick
Quest Software|||This is a sample of one of the long running queries. Keep in mind the sample
I have included here is only about a quarter of the entire statement. Care t
o
explain how this might block other queries so that a newbie like me can
understand?
(
SELECT
ol.KY_OPINION_CD_ID AS KY_OPINION_CD_ID,
min(SKU_NOTE_2) AS SKU_NOTE_2,
min(SKU_NOTE) AS SKU_NOTE,
SKU_01 AS CURR_GROUP_CD,
SKU_01 AS CURR_GROUP_TEXT,
'SKU_01' AS GROUP_ORDER_CD,
COUNT(*) AS NUM_CHILDREN,
'2559946|SKU' AS AGGR_NAME,
2626210 AS AGGR_ID,
Min(cs.KY_SKU_ID) AS KY_SKU_ID,
Min(cs.KY_CUST_ID) as KY_CUST_ID,
min(CUST_CD) AS CUST_CD,
min(SKU_CD) AS SKU_CD,
min(BUS_UNIT_CD) AS BUS_UNIT_CD,
min(CUST_01) as CUST_01,
min(CUST_02) as CUST_02,
min(CUST_03) as CUST_03,
min(CUST_04) as CUST_04,
min(CUST_05) as CUST_05,
min(CUST_06) as CUST_06,
min(CUST_07) as CUST_07,
min(CUST_08) as CUST_08,
min(SKU_01) as SKU_01,
min(SKU_02) as SKU_02,
min(SKU_03) as SKU_03,
min(SKU_04) as SKU_04,
min(SKU_05) as SKU_05,
min(SKU_06) as SKU_06,
min(SKU_07) as SKU_07,
min(SKU_08) as SKU_08,
min(SKU_09) as SKU_09,
min(SKU_10) as SKU_10,
min(SKU_11) as SKU_11,
min(SKU_12) as SKU_12,
min(SKU_13) as SKU_13,
min(SKU_14) as SKU_14,
min(SKU_15) as SKU_15,
min(SKU_16) as SKU_16,
min(SKU_17) as SKU_17,
min(SKU_18) as SKU_18,
min(SKU_19) as SKU_19,
min(SKU_20) as SKU_20,
min(SKU_21) as SKU_21,
min(SKU_22) as SKU_22,
min(SKU_23) as SKU_23,
min(SKU_24) as SKU_24,
min(SKU_25) as SKU_25,
min(SKU_26) as SKU_26,
min(SKU_27) as SKU_27,
min(SKU_28) as SKU_28,
min(SKU_29) as SKU_29,
min(SKU_30) as SKU_30,
min(SKU_31) as SKU_31,
min(SKU_32) as SKU_32,
min(SKU_33) as SKU_33,
min(SKU_34) as SKU_34,
avg(SKU_AMT_01) as SKU_AMT_01,
avg(SKU_AMT_02) as SKU_AMT_02,
avg(SKU_AMT_03) as SKU_AMT_03,
min(SKU_AMT_04) as SKU_AMT_04,
avg(SKU_AMT_05) as SKU_AMT_05,
min(SKU_DATE_01) as SKU_DATE_01,
min(SKU_DATE_02) as SKU_DATE_02,
min(SKU_DATE_03) as SKU_DATE_03,
max(CUST_SKU_01) as CUST_SKU_01,
min(CUST_SKU_02) as CUST_SKU_02,
min(CUST_SKU_03) as CUST_SKU_03,
min(CUST_SKU_04) as CUST_SKU_04,
max(CUST_SKU_05) as CUST_SKU_05,
min(CUST_SKU_06) as CUST_SKU_06,
min(CUST_SKU_07) as CUST_SKU_07,
min(CUST_SKU_08) as CUST_SKU_08,
min(CUST_SKU_09) as CUST_SKU_09,
min(CUST_SKU_10) as CUST_SKU_10,
min(CUST_SKU_11) as CUST_SKU_11,
min(CUST_SKU_12) as CUST_SKU_12,
min(CUST_SKU_13) as CUST_SKU_13,
min(CUST_SKU_14) as CUST_SKU_14,
min(CUST_SKU_15) as CUST_SKU_15,
max(CUST_SKU_16) as CUST_SKU_16,
max(CUST_SKU_17) as CUST_SKU_17,
max(CUST_SKU_18) as CUST_SKU_18,
max(CUST_SKU_19) as CUST_SKU_19,
max(CUST_SKU_20) as CUST_SKU_20,
max(CUST_SKU_21) as CUST_SKU_21,
max(CUST_SKU_22) as CUST_SKU_22,
max(CUST_SKU_23) as CUST_SKU_23,
max(CUST_SKU_24) as CUST_SKU_24,
max(CUST_SKU_25) as CUST_SKU_25,
max(CUST_SKU_26) as CUST_SKU_26,
max(CUST_SKU_27) as CUST_SKU_27,
max(CUST_SKU_30) as CUST_SKU_30,
avg(CUST_SKU_AMT_01) as CUST_SKU_AMT_01,
avg(CUST_SKU_AMT_02) as CUST_SKU_AMT_02,
avg(CUST_SKU_AMT_03) as CUST_SKU_AMT_03,
avg(CUST_SKU_AMT_04) as CUST_SKU_AMT_04,
avg(CUST_SKU_AMT_05) as CUST_SKU_AMT_05,
avg(CUST_SKU_AMT_06) as CUST_SKU_AMT_06,
avg(CUST_SKU_AMT_07) as CUST_SKU_AMT_07,
avg(CUST_SKU_AMT_08) as CUST_SKU_AMT_08,
sum(CUST_SKU_AMT_09) as CUST_SKU_AMT_09,
sum(CUST_SKU_AMT_10) as CUST_SKU_AMT_10,
sum(CUST_SKU_AMT_11) as CUST_SKU_AMT_11,
sum(CUST_SKU_AMT_11*p1.OPINION_QTY_01) as CUST_SKU_AMT_12,
'UOM' AS MIN_UM,
'UOM' AS MAX_UM,
(SELECT COUNT(*) from tblNotes n with(rowlock)
WHERE n.KY_SKU_ID = min(cs.KY_SKU_ID)
and NOTE_TYPE IS NOT NULL) AS HAS_NOTE,
0 AS DIRTY_OP,
SPACE(100) AS UPDATE_FLDS,
(SUM(ol.OPINION_QTY_N12)) as OPINION_QTY_N12,
(SUM(ol.OPINION_QTY_N11)) as OPINION_QTY_N11,
(SUM(ol.OPINION_QTY_N10)) as OPINION_QTY_N10,
(SUM(ol.OPINION_QTY_N09)) as OPINION_QTY_N09,
(SUM(ol.OPINION_QTY_N08)) as OPINION_QTY_N08,
(SUM(ol.OPINION_QTY_N07)) as OPINION_QTY_N07,
(SUM(ol.OPINION_QTY_N06)) as OPINION_QTY_N06,
(SUM(ol.OPINION_QTY_N05)) as OPINION_QTY_N05,
(SUM(ol.OPINION_QTY_N04)) as OPINION_QTY_N04,
(SUM(ol.OPINION_QTY_N03)) as OPINION_QTY_N03,
(SUM(ol.OPINION_QTY_N02)) as OPINION_QTY_N02,
(SUM(ol.OPINION_QTY_N01)) as OPINION_QTY_N01,
(SUM(ol.OPINION_QTY_01)) as OPINION_QTY_01,
(SUM(ol.OPINION_QTY_02)) as OPINION_QTY_02,
(SUM(ol.OPINION_QTY_03)) as OPINION_QTY_03,
(SUM(ol.OPINION_QTY_04)) as OPINION_QTY_04,
(SUM(ol.OPINION_QTY_05)) as OPINION_QTY_05,
(SUM(ol.OPINION_QTY_06)) as OPINION_QTY_06,
(SUM(ol.OPINION_QTY_07)) as OPINION_QTY_07,
(SUM(ol.OPINION_QTY_08)) as OPINION_QTY_08,
(SUM(ol.OPINION_QTY_09)) as OPINION_QTY_09,
(SUM(ol.OPINION_QTY_10)) as OPINION_QTY_10,
(SUM(ol.OPINION_QTY_11)) as OPINION_QTY_11,
(SUM(ol.OPINION_QTY_12)) as OPINION_QTY_12,
(SUM(ol.OPINION_QTY_13)) as OPINION_QTY_13,
(SUM(ol.OPINION_QTY_14)) as OPINION_QTY_14,
(SUM(ol.OPINION_QTY_15)) as OPINION_QTY_15,
(SUM(ol.OPINION_QTY_16)) as OPINION_QTY_16,
(SUM(ol.OPINION_QTY_17)) as OPINION_QTY_17,
(SUM(ol.OPINION_QTY_18)) as OPINION_QTY_18,
(SUM(ol.OPINION_QTY_19)) as OPINION_QTY_19,
(SUM(ol.OPINION_QTY_20)) as OPINION_QTY_20,
(SUM(ol.OPINION_QTY_21)) as OPINION_QTY_21,
(SUM(ol.OPINION_QTY_22)) as OPINION_QTY_22,
(SUM(ol.OPINION_QTY_23)) as OPINION_QTY_23,
(SUM(ol.OPINION_QTY_24)) as OPINION_QTY_24,
(SUM(ol.OPINION_QTY_25)) as OPINION_QTY_25,
(SUM(ol.OPINION_QTY_26)) as OPINION_QTY_26,
(SUM(ol.OPINION_QTY_27)) as OPINION_QTY_27,
(SUM(ol.OPINION_QTY_28)) as OPINION_QTY_28,
(SUM(ol.OPINION_QTY_29)) as OPINION_QTY_29,
(SUM(ol.OPINION_QTY_30)) as OPINION_QTY_30,
(SUM(ol.OPINION_QTY_31)) as OPINION_QTY_31,
(SUM(ol.OPINION_QTY_32)) as OPINION_QTY_32,
(SUM(ol.OPINION_QTY_33)) as OPINION_QTY_33,
(SUM(ol.OPINION_QTY_34)) as OPINION_QTY_34,
(SUM(ol.OPINION_QTY_35)) as OPINION_QTY_35,
(SUM(ol.OPINION_QTY_36)) as OPINION_QTY_36,
(SUM(ol.OPINION_AMT_N24)) AS OPINION_AMT_N24,
(SUM(ol.OPINION_AMT_N23)) AS OPINION_AMT_N23,
(SUM(ol.OPINION_AMT_N22)) AS OPINION_AMT_N22,
(SUM(ol.OPINION_AMT_N21)) AS OPINION_AMT_N21,
(SUM(ol.OPINION_AMT_N20)) AS OPINION_AMT_N20,
(SUM(ol.OPINION_AMT_N19)) AS OPINION_AMT_N19,
(SUM(ol.OPINION_AMT_N18)) AS OPINION_AMT_N18,
(SUM(ol.OPINION_AMT_N17)) AS OPINION_AMT_N17,
(SUM(ol.OPINION_AMT_N16)) AS OPINION_AMT_N16,
(SUM(ol.OPINION_AMT_N15)) AS OPINION_AMT_N15,
(SUM(ol.OPINION_AMT_N14)) AS OPINION_AMT_N14,
(SUM(ol.OPINION_AMT_N13)) AS OPINION_AMT_N13,
(SUM(ol.OPINION_AMT_N12)) AS OPINION_AMT_N12,
(SUM(ol.OPINION_AMT_N11)) AS OPINION_AMT_N11,
(SUM(ol.OPINION_AMT_N10)) AS OPINION_AMT_N10,
(SUM(ol.OPINION_AMT_N09)) AS OPINION_AMT_N09,
(SUM(ol.OPINION_AMT_N08)) AS OPINION_AMT_N08,
(SUM(ol.OPINION_AMT_N07)) AS OPINION_AMT_N07,
(SUM(ol.OPINION_AMT_N06)) AS OPINION_AMT_N06,
(SUM(ol.OPINION_AMT_N05)) AS OPINION_AMT_N05,
(SUM(ol.OPINION_AMT_N04)) AS OPINION_AMT_N04,
(SUM(ol.OPINION_AMT_N03)) AS OPINION_AMT_N03,
(SUM(ol.OPINION_AMT_N02)) AS OPINION_AMT_N02,
(SUM(ol.OPINION_AMT_N01)) AS OPINION_AMT_N01,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_01*p3.OPINION_QTY_01)
else 0 end) AS OPINION_AMT_01,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_02*p3.OPINION_QTY_02)
else 0 end) AS OPINION_AMT_02,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_03*p3.OPINION_QTY_03)
else 0 end) AS OPINION_AMT_03,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_04*p3.OPINION_QTY_04)
else 0 end) AS OPINION_AMT_04,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_05*p3.OPINION_QTY_05)
else 0 end) AS OPINION_AMT_05,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_06*p3.OPINION_QTY_06)
else 0 end) AS OPINION_AMT_06,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_07*p3.OPINION_QTY_07)
else 0 end) AS OPINION_AMT_07,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_08*p3.OPINION_QTY_08)
else 0 end) AS OPINION_AMT_08,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_09*p3.OPINION_QTY_09)
else 0 end) AS OPINION_AMT_09,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_10*p3.OPINION_QTY_10)
else 0 end) AS OPINION_AMT_10,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_11*p3.OPINION_QTY_11)
else 0 end) AS OPINION_AMT_11,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_12*p3.OPINION_QTY_12)
else 0 end) AS OPINION_AMT_12,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_13*p3.OPINION_QTY_13)
else 0 end) AS OPINION_AMT_13,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_14*p3.OPINION_QTY_14)
else 0 end) AS OPINION_AMT_14,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_15*p3.OPINION_QTY_15)
else 0 end) AS OPINION_AMT_15,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_16*p3.OPINION_QTY_16)
else 0 end) AS OPINION_AMT_16,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_17*p3.OPINION_QTY_17)
else 0 end) AS OPINION_AMT_17,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_18*p3.OPINION_QTY_18)
else 0 end) AS OPINION_AMT_18,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_19*p3.OPINION_QTY_19)
else 0 end) AS OPINION_AMT_19,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_20*p3.OPINION_QTY_20)
else 0 end) AS OPINION_AMT_20,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_21*p3.OPINION_QTY_21)
else 0 end) AS OPINION_AMT_21,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_22*p3.OPINION_QTY_22)
else 0 end) AS OPINION_AMT_22,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_23*p3.OPINION_QTY_23)
else 0 end) AS OPINION_AMT_23,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_24*p3.OPINION_QTY_24)
else 0 end) AS OPINION_AMT_24,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_25*p3.OPINION_QTY_25)
else 0 end) AS OPINION_AMT_25,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_26*p3.OPINION_QTY_26)
else 0 end) AS OPINION_AMT_26,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_27*p3.OPINION_QTY_27)
else 0 end) AS OPINION_AMT_27,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_28*p3.OPINION_QTY_28)
else 0 end) AS OPINION_AMT_28,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_29*p3.OPINION_QTY_29)
else 0 end) AS OPINION_AMT_29,
case ...
from ...
where ...
David Gugick wrote:
>[quoted text clipped - 4 lines]
>Sounds like you are having blocking issues. That is, one query is blocking
>other queries and because of that they are executing in a serial fashion.
>Check the execution plans of the queries in the "task" to make sure they ar
e
>properly using indexes and make sure you avoid long running transactions.
>Feel free to post the table DDL, indexes, queries in question, and the
>execution plans for each if you have any questions. You can check much of
>this information from Profiler. Use the SQL:BatchCompleted and RPC:Complete
d
>events for a high-level performance overview. If you want more detail add
>SQL:StmtCompleted and SP:StmtCompleted events. Look over the cpu, duration,
>and reads columns.
>
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200601/1|||Robert R via droptable.com wrote:
> This is a sample of one of the long running queries. Keep in mind the
> sample I have included here is only about a quarter of the entire
> statement. Care to explain how this might block other queries so that
> a newbie like me can understand?
> <snip>
Where did that SQL statement come from? It looks as though your table is
denormalized. I see a SKU_1 through SKU_34 that appear to be columns in
the table. If that's true, table design issues aside, that's an
extremely large and complex query that could very well be accessing the
entire table each time it's executed and / or causing extremely high IO
load on your disk subsystem and / or causing high CPU. You are also
returning a SPACE(100) as a column which is a little strange.
Denormalized tables cause large row sizes also very low row density
which means a lot of page reads. Excessive page reads mean a lot of
physical IO which is slow. A lot of aggregations means higher CPU
utilization. All of these items can translate in to slow executing
queries, long transaction times, extended locks that cause blocking,
high disk IO, hign CPU, etc.
So, first we need to know if your table really looks like what I see in
the query. If so, the answers here will likely point to the table design
as the primary problem. It would be impossible for anyone here to
recommend a solution for the query itself without knowing exactly what
the tables look like, what indexes are in place, what you're hoping to
return from the query, and a complete query with parameters/bind
variables.
Sorry I can't be more help. THe best thing to do is take a step back and
make a new post with all the relevant information.
David Gugick
Quest Software
www.quest.com

queries hanging?

I am running SQL2000, occasionally queries take a huge amount of time to
process, for some reason they seem to pause for a couple of minutes. For
example, the following query normally executes in a couple of seconds...
SELECT count(*)
FROM object ob
JOIN quantity qt ON ob.objid=qt.objid
JOIN site st ON qt.siteid=st.siteid
JOIN cussite cs ON cs.siteid=st.siteid
WHERE cs.cusid=4900 AND ob.link=1 AND qt.lupd>'1/1/1900'
but for some reason, today it is taking over 10 minutes!!
Running sp_lock2 reveals..
TABLE LOCKTYPE MODE
OBJECT TAB Sch-S
QUANTITY TAB Sch-S
SITE TAB Sch-S
CUSSITE TAB Sch-S
The query just sits there with those locks for over 10 minutes before coming
back with the data.
Any help much appreciated...
MarkFirst, check the query plans. Is the query using the index? Your statistic
s
might not be up to date.
Second, is there something else on that machine using all the processor and
disk? If something else is hogging all of the resources, then your query is
just going to have to wait.
I hope this helps.
--
Russel Loski, MCSD.Net
"Mark Baldwin" wrote:

> I am running SQL2000, occasionally queries take a huge amount of time to
> process, for some reason they seem to pause for a couple of minutes. For
> example, the following query normally executes in a couple of seconds...
> SELECT count(*)
> FROM object ob
> JOIN quantity qt ON ob.objid=qt.objid
> JOIN site st ON qt.siteid=st.siteid
> JOIN cussite cs ON cs.siteid=st.siteid
> WHERE cs.cusid=4900 AND ob.link=1 AND qt.lupd>'1/1/1900'
> but for some reason, today it is taking over 10 minutes!!
> Running sp_lock2 reveals..
> TABLE LOCKTYPE MODE
> OBJECT TAB Sch-S
> QUANTITY TAB Sch-S
> SITE TAB Sch-S
> CUSSITE TAB Sch-S
> The query just sits there with those locks for over 10 minutes before comi
ng
> back with the data.
> Any help much appreciated...
> Mark
>
>|||Mark Baldwin wrote:
> I am running SQL2000, occasionally queries take a huge amount of time to
> process, for some reason they seem to pause for a couple of minutes. For
> example, the following query normally executes in a couple of seconds...
> SELECT count(*)
> FROM object ob
> JOIN quantity qt ON ob.objid=qt.objid
> JOIN site st ON qt.siteid=st.siteid
> JOIN cussite cs ON cs.siteid=st.siteid
> WHERE cs.cusid=4900 AND ob.link=1 AND qt.lupd>'1/1/1900'
> but for some reason, today it is taking over 10 minutes!!
> Running sp_lock2 reveals..
> TABLE LOCKTYPE MODE
> OBJECT TAB Sch-S
> QUANTITY TAB Sch-S
> SITE TAB Sch-S
> CUSSITE TAB Sch-S
> The query just sits there with those locks for over 10 minutes before comi
ng
> back with the data.
> Any help much appreciated...
> Mark
>
Review the execution plan, make sure the proper indexes are there to
support the query, check for blocking, all the typical things...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I wish I could review the execution plan! In query analyser, selecting
"display estimated execution plan" with noexec or simply ticking the
"display estimated execution plan" option results in a long pause (10minutes
or more) before coming back with an empty execution plan and no error.
This is the same pause that affects my queries. So it's not the query thats
taking the time, its the pre processing of the query.
Best regards
Mark
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45B63900.5090009@.realsqlguy.com...
> Mark Baldwin wrote:
> Review the execution plan, make sure the proper indexes are there to
> support the query, check for blocking, all the typical things...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Mark Baldwin wrote:
> I wish I could review the execution plan! In query analyser, selecting
> "display estimated execution plan" with noexec or simply ticking the
> "display estimated execution plan" option results in a long pause (10minut
es
> or more) before coming back with an empty execution plan and no error.
> This is the same pause that affects my queries. So it's not the query that
s
> taking the time, its the pre processing of the query.
>
Are your statistics current? Maybe something here will help:
http://groups.google.com/group/micr...7eea6596a0b6011
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Look at your Task Manager on the *client*. Is something taking up a lot
of CPU on it? What about the server?
-Dave
Mark Baldwin wrote:
> I wish I could review the execution plan! In query analyser, selecting
> "display estimated execution plan" with noexec or simply ticking the
> "display estimated execution plan" option results in a long pause (10minut
es
> or more) before coming back with an empty execution plan and no error.
> This is the same pause that affects my queries. So it's not the query that
s
> taking the time, its the pre processing of the query.
>
-Dave Markle
http://www.markleconsulting.com/blog

queries hanging?

I am running SQL2000, occasionally queries take a huge amount of time to
process, for some reason they seem to pause for a couple of minutes. For
example, the following query normally executes in a couple of seconds...
SELECT count(*)
FROM object ob
JOIN quantity qt ON ob.objid=qt.objid
JOIN site st ON qt.siteid=st.siteid
JOIN cussite cs ON cs.siteid=st.siteid
WHERE cs.cusid=4900 AND ob.link=1 AND qt.lupd>'1/1/1900'
but for some reason, today it is taking over 10 minutes!!
Running sp_lock2 reveals..
TABLE LOCKTYPE MODE
OBJECT TAB Sch-S
QUANTITY TAB Sch-S
SITE TAB Sch-S
CUSSITE TAB Sch-S
The query just sits there with those locks for over 10 minutes before coming
back with the data.
Any help much appreciated...
MarkMark Baldwin wrote:
> I am running SQL2000, occasionally queries take a huge amount of time to
> process, for some reason they seem to pause for a couple of minutes. For
> example, the following query normally executes in a couple of seconds...
> SELECT count(*)
> FROM object ob
> JOIN quantity qt ON ob.objid=qt.objid
> JOIN site st ON qt.siteid=st.siteid
> JOIN cussite cs ON cs.siteid=st.siteid
> WHERE cs.cusid=4900 AND ob.link=1 AND qt.lupd>'1/1/1900'
> but for some reason, today it is taking over 10 minutes!!
> Running sp_lock2 reveals..
> TABLE LOCKTYPE MODE
> OBJECT TAB Sch-S
> QUANTITY TAB Sch-S
> SITE TAB Sch-S
> CUSSITE TAB Sch-S
> The query just sits there with those locks for over 10 minutes before coming
> back with the data.
> Any help much appreciated...
> Mark
>
Review the execution plan, make sure the proper indexes are there to
support the query, check for blocking, all the typical things...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I wish I could review the execution plan! In query analyser, selecting
"display estimated execution plan" with noexec or simply ticking the
"display estimated execution plan" option results in a long pause (10minutes
or more) before coming back with an empty execution plan and no error.
This is the same pause that affects my queries. So it's not the query thats
taking the time, its the pre processing of the query.
--
Best regards
Mark
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45B63900.5090009@.realsqlguy.com...
> Mark Baldwin wrote:
>> I am running SQL2000, occasionally queries take a huge amount of time to
>> process, for some reason they seem to pause for a couple of minutes. For
>> example, the following query normally executes in a couple of seconds...
>> SELECT count(*)
>> FROM object ob
>> JOIN quantity qt ON ob.objid=qt.objid
>> JOIN site st ON qt.siteid=st.siteid
>> JOIN cussite cs ON cs.siteid=st.siteid
>> WHERE cs.cusid=4900 AND ob.link=1 AND qt.lupd>'1/1/1900'
>> but for some reason, today it is taking over 10 minutes!!
>> Running sp_lock2 reveals..
>> TABLE LOCKTYPE MODE
>> OBJECT TAB Sch-S
>> QUANTITY TAB Sch-S
>> SITE TAB Sch-S
>> CUSSITE TAB Sch-S
>> The query just sits there with those locks for over 10 minutes before
>> coming back with the data.
>> Any help much appreciated...
>> Mark
> Review the execution plan, make sure the proper indexes are there to
> support the query, check for blocking, all the typical things...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Mark Baldwin wrote:
> I wish I could review the execution plan! In query analyser, selecting
> "display estimated execution plan" with noexec or simply ticking the
> "display estimated execution plan" option results in a long pause (10minutes
> or more) before coming back with an empty execution plan and no error.
> This is the same pause that affects my queries. So it's not the query thats
> taking the time, its the pre processing of the query.
>
Are your statistics current? Maybe something here will help:
http://groups.google.com/group/microsoft.public.sqlserver.server/browse_frm/thread/90a81b1ebd25891d/c7eea6596a0b6011
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Look at your Task Manager on the *client*. Is something taking up a lot
of CPU on it? What about the server?
-Dave
Mark Baldwin wrote:
> I wish I could review the execution plan! In query analyser, selecting
> "display estimated execution plan" with noexec or simply ticking the
> "display estimated execution plan" option results in a long pause (10minutes
> or more) before coming back with an empty execution plan and no error.
> This is the same pause that affects my queries. So it's not the query thats
> taking the time, its the pre processing of the query.
>
-Dave Markle
http://www.markleconsulting.com/blogsql