Hey it's not often the blindman asks for advice on sql coding (never, I think), so here is an opportunity to solve a problem I've been knocking my head against for two days.
Here is sample code for setting up the problem:create table #blindman
(pkey smallint primary key,
fkey char(1),
updated datetime)
insert into #blindman (pkey, fkey, updated)
select 1, 'A', '1/1/2006'
UNION
select 2, 'B', '1/1/2006'
UNION
select 3, 'A', '1/2/2006'
UNION
select 4, 'B', '1/2/2006'
UNION
select 5, 'A', '1/4/2006'
UNION
select 6, 'B', '1/2/2006'
UNION
select 7, 'A', '1/3/2006'
UNION
select 8, 'B', '1/3/2006'
UNION
select 9, 'A', '1/5/2006'
UNION
select 10, 'B', '1/5/2006'
drop table #blindman
Notice that for fkey 'B', there are two entries with '1/2/2006', and for fkey 'A' the updated values are not in synch with the order of the primary key.
The challenge: determine the next pkey for each pkey value, ordered by [updated], and using pkey as a tie-breaker when two records have the same [updated] value.
Here is the desired output for the sample data:pkey fkey updated nextpkey
-- -- ---- ---
1 A 2006-01-01 3
3 A 2006-01-02 7
7 A 2006-01-03 5
5 A 2006-01-04 9
2 B 2006-01-01 4
4 B 2006-01-02 6
6 B 2006-01-02 8
8 B 2006-01-03 10
Records 9 and 10 are missing because they have not succeeding records, though I'd be just has satisfied to include them with NULL as their nextpkey value.
Ideally, I want this as a VIEW.
Who's up for the challenge?Where's the challange?
SELECT *
FROM #blindman a
LEFT JOIN #blindman b
ON a.fkey = b.fkey
AND b.pkey = (SELECT MIN(c.pkey)
FROM #blindman c
WHERE c.pkey > a.pkey
AND c.fkey = a.fkey
GROUP BY c.fkey )|||Here's a quick and dirty solution that works. I'm not in your leaque, so I'm sure you can clean it up:
CREATE PROCEDURE [dbo].[procBlindman] AS
SELECT pkey, fkey, updated, IDENTITY(int, 1,1) AS Sequence
INTO #temp
FROM blindman Order BY fkey, updated
SELECT *,
(SELECT pkey FROM #temp AS Alias2 WHERE Sequence = (SELECT Min(Sequence) FROM #temp AS Alias
WHERE Alias.Sequence > #temp.Sequence
AND Alias.fkey = #temp.fkey)) AS NextPkey
FROM #temp
GO|||Does SQL2000 vs SQL 2005 matter? OR can you use bells and whistles from either?|||Brett, your solution does not give the correct output. It gives this:pkey fkey updated nextpkey
-- -- ---------------- ---
1 A 2006-01-01 00:00:00.000 3
3 A 2006-01-02 00:00:00.000 5
7 A 2006-01-03 00:00:00.000 9
5 A 2006-01-04 00:00:00.000 7
9 A 2006-01-05 00:00:00.000 NULL
2 B 2006-01-01 00:00:00.000 4
4 B 2006-01-02 00:00:00.000 6
6 B 2006-01-02 00:00:00.000 8
8 B 2006-01-03 00:00:00.000 10
10 B 2006-01-05 00:00:00.000 NULLThe nextpkey for fkey 'A' should be in the order 3, 7, 5, 9. Your solution has record 3 being followed by record 5, when record seven is the next to follow.
pbaldy, your solution may be the one I have to go with, either using a temp table or a hideously large subquery to assign ordinal value, but I'd rather avoid the first method because I want this as a view, and the second method I fear will be very slow.|||Dude,
I'm hurt and dismayed
All you need is an ORDER BY
SELECT a.pkey, a.fkey, a.updated, b.pkey
FROM #blindman a
LEFT JOIN #blindman b
ON a.fkey = b.fkey
AND b.pkey = (SELECT MIN(c.pkey)
FROM #blindman c
WHERE c.pkey > a.pkey
AND c.fkey = a.fkey
GROUP BY c.fkey )
ORDER BY a.fkey, a.updated
This Returns
pkey fkey updated pkey
-- -- ---------------- --
1 A 2006-01-01 00:00:00.000 3
3 A 2006-01-02 00:00:00.000 5
7 A 2006-01-03 00:00:00.000 9
5 A 2006-01-04 00:00:00.000 7
9 A 2006-01-05 00:00:00.000 NULL
2 B 2006-01-01 00:00:00.000 4
4 B 2006-01-02 00:00:00.000 6
6 B 2006-01-02 00:00:00.000 8
8 B 2006-01-03 00:00:00.000 10
10 B 2006-01-05 00:00:00.000 NULL
(10 row(s) affected)
[code]|||Desired output:pkey fkey updated nextpkey
-- -- ---- ---
1 A 2006-01-01 3
3 A 2006-01-02 7
7 A 2006-01-03 5
5 A 2006-01-04 9
Brett's outputpkey fkey updated pkey
-- -- ---------------- --
1 A 2006-01-01 00:00:00.000 3
3 A 2006-01-02 00:00:00.000 5
7 A 2006-01-03 00:00:00.000 9
5 A 2006-01-04 00:00:00.000 7
One of these things is not like the other.
One of these things is kinda-the-same.
One of these things is not like the other.
Can you tell me now before I finish my song?|||OK, so you want it by date...the comparison then just shift to look at that...
Let me fire up QA...but it's really not that big of a change|||Does this float your boat?SELECT a.pkey, a.fkey, a.updated, Coalesce(Min(b.pkey), Min(c.pkey)) AS nextpkey
FROM #blindman AS a
LEFT JOIN #blindman AS b
ON (b.fkey = a.fkey
AND b.updated = a.updated
AND a.pkey < b.pkey)
LEFT JOIN #blindman AS c
ON (c.fkey = a.fkey
AND c.updated = (SELECT Min(d.updated)
FROM #blindman AS d
WHERE d.fkey = a.fkey
AND a.updated < d.updated))
GROUP BY a.fkey, a.updated, a.pkey
ORDER BY a.fkey, a.updated, a.pkey
-PatP|||A double outer-join with coalesce. Very creative. Definitely an "outside the box" solution. I will try applying it to my actual problem.
Thanks.|||As I suspected, there was a better solution (the plan scales much better for large result sets):SELECT a.pkey, a.fkey, a.updated, Coalesce(
(SELECT Min(b.pkey)
FROM #blindman AS b
WHERE b.fkey = a.fkey
AND b.updated = a.updated
AND a.pkey < b.pkey)
, (SELECT Min(c.pkey)
FROM #blindman AS c
WHERE c.fkey = a.fkey
AND c.updated = (SELECT Min(d.updated)
FROM #blindman AS d
WHERE d.fkey = a.fkey
AND a.updated < d.updated))) AS nextpkey
FROM #blindman AS a
ORDER BY a.fkey, a.updated, a.pkey-PatP|||I missed the challenge(actually I tried ;) today,didnt got the solution).Anyway congratulation Pat.U deserve a :beer:|||OK, and the reason for this is?|||Its a homework assignment.
;)
Actually, the example given is a simplified version of a view I need to develop for a project.|||sounds like you need some table normalization|||Not a normalization issue. The problem is that the sequence of the surrogate key is not in sync with the order of the natural key. But since this is pulling from the source database, it's not a schema I could change anyway.|||And the coding challenge winner is....Pat!
Very impressive SQL, dude.
You should know that while both solutions produced correct results, your first solution generated an "internal sql server error" when added as a subquery. Apparently, too complex for the optimizer to handle.
The second version did run about twice as fast, and shoe-horned into my final code without error. And just so you know this WASN'T a homework assignment, here is the final code. See if you can find your logic in it!
select case ClaimReserve.ReserveType when 'med' then 'M' when 'comp' then 'I' end as LOSS_DETAIL_ID,
Convert(varchar(50), ClaimReserve.fkClaim) as CLAIM_ID,
replace(replace(replace(convert(char(16), ClaimReserve.DateLup, 120), '-', ''), ' ', ''), ':', '') + convert(varchar(20), ClaimReserve.pkClaimReserve) as ACTIVITY_SEQUENCE,
case when ClaimReserve.ReserveAmt = isnull(PTD1.MedicalPTD, 0) + isnull(PTD2.PharmacyPTD, 0) then 'CL'
when PriorClaimReserve.PriorpkClaimReserve is null then 'OC'
else 'RC'
end as ACTIVITY_TYPE_CODE,
ClaimReserve.DateLup as DATE_OF_TRANSACTION,
case when ClaimReserve.ReserveAmt = isnull(MedicalPTD, 0) + isnull(PharmacyPTD, 0) then 0 --CL
when PriorClaimReserve.PriorpkClaimReserve is null then ClaimReserve.ReserveAmt --OC
else ClaimReserve.ReserveAmt - coalesce(PriorClaimReserve.PriorReserveAmt, 0) --RC
end as RESERVE_CHANGE_AMOUNT,
null as TRANSACTION_TYPE_CODE,
null as PAYMENT_AMOUNT
from ClaimReserve
left outer join --PriorClaimReserve
(SELECT Coalesce(
(SELECT Min(b.pkClaimReserve)
FROM ClaimReserve AS b
WHERE b.fkClaim = a.fkClaim
AND b.DateLup = a.DateLup
AND a.pkClaimReserve < b.pkClaimReserve),
(SELECT Min(c.pkClaimReserve)
FROM ClaimReserve AS c
WHERE c.fkClaim = a.fkClaim
AND c.DateLup =
(SELECT Min(d.DateLup)
FROM ClaimReserve AS d
WHERE d.fkClaim = a.fkClaim
AND a.DateLup < d.DateLup))) AS pkClaimReserve,
a.pkClaimReserve as PriorpkClaimReserve,
a.ReserveAmt as PriorReserveAmt
FROM ClaimReserve AS a) PriorClaimReserve
on ClaimReserve.pkClaimReserve = PriorClaimReserve.pkClaimReserve
left outer join --PTD1
(Select CashFlow.fkClaim,
sum(Voucher.Amount) as MedicalPTD
from Voucher
inner join CashFlow on CashFlow.pkCashFlow = Voucher.fkCashFlow
Where Voucher.Status <> 'Reversed'
and Voucher.ParentType = 'BillHeader'
group by CashFlow.fkClaim) PTD1
on ClaimReserve.fkClaim = PTD1.fkClaim
left outer join --PTD2
(Select Cashflow.fkClaim,
isnull(sum(Voucher.Amount), 0) as PharmacyPTD
from Cashflow
inner join Voucher on Cashflow.pkCashFlow = Voucher.fkCashFlow
inner join ExpenseHeader
on Voucher.fkParent = ExpenseHeader.pkExpenseHeader
and Voucher.ParentType = 'ExpenseHeader'
Where ExpenseHeader.ExpenseType = 4
and Voucher.Status <> 'Reversed'
group by CashFlow.fkClaim) PTD2
on ClaimReserve.fkClaim = PTD2.fkClaim
where ClaimReserve.ReserveType not in ('exp', 'legal')
Showing posts with label solve. Show all posts
Showing posts with label solve. Show all posts
Thursday, March 22, 2012
A Challenging Query
Hi EveryBody,
Here is a very interesting SQL which I failed to solve.
I have two tables called Main and Notes.Table notes has id field which is
primary key of the Main table and foreign key in notes table. Notes table has
an identity column called NotesId.
What I have to do is that I have to show all data of main table but they
will be ordered depending upon a particular value of the note field in the
notes table. What I mean is : say there is ID 100,101,102,103 existing in the
main table. They may have several entries in the notes table and some of
those entries containing say "Desired match" in their note field.
What I want : in time of selection those entries who have "Desired Match" in
their notes field they will be coming first in their chronological order.
But their are some constraints : you cannot use any group by or distinct
clause in the query. and the resultant data cannot have any duplicate rows
Here is what I tried :
select top 100 percent main.*,notes.note,notes.date
from main left join notes on main.id=notes.id
order by convert(
numeric,
case
when notes.note like('Desired%') then '100000'
else
'500'
end
) desc,notes.date asc
But problem is that this result set contains duplicate data
Any new or better Idea.
Thanks
Kaushik
On Wed, 23 Mar 2005 22:23:01 -0800, Kaushik wrote:
>Hi EveryBody,
>Here is a very interesting SQL which I failed to solve.
(snip)
Hi Kaushik,
I love solving SQL challenges. But I'm not quite as good at trying to
understand verbose descriptions. I'll refer you to a website that
explains what information you should include in a post in order for us
to help you: www.aspfaq.com/5006. If you follow the guidelines given
there, I'll probably be able to help you.
>But their are some constraints : you cannot use any group by or distinct
>clause in the query. and the resultant data cannot have any duplicate rows
I understand the need to eliminate duplicate rows, but the constraints
to not use GROUP BY or DISTINCT makes no sense to me. Can you explain
the reason for this restriction? Because I really don't understand why
any SQL coder would voluntarily part with part of the tools he needs to
do his job.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
Here is a very interesting SQL which I failed to solve.
I have two tables called Main and Notes.Table notes has id field which is
primary key of the Main table and foreign key in notes table. Notes table has
an identity column called NotesId.
What I have to do is that I have to show all data of main table but they
will be ordered depending upon a particular value of the note field in the
notes table. What I mean is : say there is ID 100,101,102,103 existing in the
main table. They may have several entries in the notes table and some of
those entries containing say "Desired match" in their note field.
What I want : in time of selection those entries who have "Desired Match" in
their notes field they will be coming first in their chronological order.
But their are some constraints : you cannot use any group by or distinct
clause in the query. and the resultant data cannot have any duplicate rows
Here is what I tried :
select top 100 percent main.*,notes.note,notes.date
from main left join notes on main.id=notes.id
order by convert(
numeric,
case
when notes.note like('Desired%') then '100000'
else
'500'
end
) desc,notes.date asc
But problem is that this result set contains duplicate data
Any new or better Idea.
Thanks
Kaushik
On Wed, 23 Mar 2005 22:23:01 -0800, Kaushik wrote:
>Hi EveryBody,
>Here is a very interesting SQL which I failed to solve.
(snip)
Hi Kaushik,
I love solving SQL challenges. But I'm not quite as good at trying to
understand verbose descriptions. I'll refer you to a website that
explains what information you should include in a post in order for us
to help you: www.aspfaq.com/5006. If you follow the guidelines given
there, I'll probably be able to help you.
>But their are some constraints : you cannot use any group by or distinct
>clause in the query. and the resultant data cannot have any duplicate rows
I understand the need to eliminate duplicate rows, but the constraints
to not use GROUP BY or DISTINCT makes no sense to me. Can you explain
the reason for this restriction? Because I really don't understand why
any SQL coder would voluntarily part with part of the tools he needs to
do his job.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
Tuesday, March 6, 2012
64-bit SQL Server 2005 Cluster: slow remote desktop
Hi,
We are moving from SQL Server 2000 to SQL Server 2005. Wondering if you
have the same problem or can share some of your experience to solve some
issue.
We have brand new clustered database servers which have Quad Duo CPUs
and 8GB memory each running SQL Server 2005 64bit Enterprise edition
with SP1 applied and on Win2K3 server x64 Enterprise edition. Only
database engine component are installed for now.
We have some strange behaviors from time to time that :
When we have some query running from some local PC Management Studio,
the remote desktop control is either takes a long time to connect or
very slow to react the requests. When it happened, sometime we can not
connect to the database server from Management Studio with the
connection timeout which was OK couple minutes ago. Interestingly at
those moments, the query is still running , no process blocking found,
very light CPU usage and low disk I/O detected from the monitoring tool
and it looks like the SQL server is not busy at all. No 3rd
party/identifiable process can be found running on the database server
also, the 'system idle process' is the main one with CPU count. And
overall the query just take very long than expected to finish. We could
have very normal days for a while and randomly we will encountered the
symptom above. Each time query we ran is different. Some are light and
some are heavy. The query we recently ran is to create index on many big
partitioned tables.
Thanks in advance.Hi,
Thanks for your post!
From your description, I understand that:
You installed SQL Server 2005 64bit as clustered servers. The Windows
Server 2003 was also 64bit.
When you executed the query in SQL Server Management Studio locally, the
remote desktop need a long time to connect the server and had very slow
responses for requests.
At that time, extra connections to the SQL Server were also slow and even
failed. The CPU use ratio and disk I/O level were low then.
This happened once a while. The latest query was to create index on many
large partitioned tables.
If I have misunderstood, please let me know.
To narrow down this issue, I appreciate you send me the performance report
files of SQL Server Profiler and Performance Monitor when the issue is
happening again.
Also, I appreciate you could tell me more about the query when this issue
happened:
What's kind of the query?
Does a simple query will cause that problem?
When this issue happened, what were recorded in the SQL error logs and
system event logs?
If you have any other concerns, please feel free to let me know. It's my
pleasure to be of assistance.
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others:
https://partner.microsoft.com/US/technicalsupport/supportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||American are in vacation "Independency Day".
Charles|||Hi ,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Have a great day!
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=============================================================This posting is provided "AS IS" with no warranties, and confers no rights.
We are moving from SQL Server 2000 to SQL Server 2005. Wondering if you
have the same problem or can share some of your experience to solve some
issue.
We have brand new clustered database servers which have Quad Duo CPUs
and 8GB memory each running SQL Server 2005 64bit Enterprise edition
with SP1 applied and on Win2K3 server x64 Enterprise edition. Only
database engine component are installed for now.
We have some strange behaviors from time to time that :
When we have some query running from some local PC Management Studio,
the remote desktop control is either takes a long time to connect or
very slow to react the requests. When it happened, sometime we can not
connect to the database server from Management Studio with the
connection timeout which was OK couple minutes ago. Interestingly at
those moments, the query is still running , no process blocking found,
very light CPU usage and low disk I/O detected from the monitoring tool
and it looks like the SQL server is not busy at all. No 3rd
party/identifiable process can be found running on the database server
also, the 'system idle process' is the main one with CPU count. And
overall the query just take very long than expected to finish. We could
have very normal days for a while and randomly we will encountered the
symptom above. Each time query we ran is different. Some are light and
some are heavy. The query we recently ran is to create index on many big
partitioned tables.
Thanks in advance.Hi,
Thanks for your post!
From your description, I understand that:
You installed SQL Server 2005 64bit as clustered servers. The Windows
Server 2003 was also 64bit.
When you executed the query in SQL Server Management Studio locally, the
remote desktop need a long time to connect the server and had very slow
responses for requests.
At that time, extra connections to the SQL Server were also slow and even
failed. The CPU use ratio and disk I/O level were low then.
This happened once a while. The latest query was to create index on many
large partitioned tables.
If I have misunderstood, please let me know.
To narrow down this issue, I appreciate you send me the performance report
files of SQL Server Profiler and Performance Monitor when the issue is
happening again.
Also, I appreciate you could tell me more about the query when this issue
happened:
What's kind of the query?
Does a simple query will cause that problem?
When this issue happened, what were recorded in the SQL error logs and
system event logs?
If you have any other concerns, please feel free to let me know. It's my
pleasure to be of assistance.
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others:
https://partner.microsoft.com/US/technicalsupport/supportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||American are in vacation "Independency Day".
Charles|||Hi ,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Have a great day!
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=============================================================This posting is provided "AS IS" with no warranties, and confers no rights.
64-bit SQL Server 2005 Cluster: slow remote desktop
Hi,
We are moving from SQL Server 2000 to SQL Server 2005. Wondering if you
have the same problem or can share some of your experience to solve some
issue.
We have brand new clustered database servers which have Quad Duo CPUs
and 8GB memory each running SQL Server 2005 64bit Enterprise edition
with SP1 applied and on Win2K3 server x64 Enterprise edition. Only
database engine component are installed for now.
We have some strange behaviors from time to time that :
When we have some query running from some local PC Management Studio,
the remote desktop control is either takes a long time to connect or
very slow to react the requests. When it happened, sometime we can not
connect to the database server from Management Studio with the
connection timeout which was OK couple minutes ago. Interestingly at
those moments, the query is still running , no process blocking found,
very light CPU usage and low disk I/O detected from the monitoring tool
and it looks like the SQL server is not busy at all. No 3rd
party/identifiable process can be found running on the database server
also, the 'system idle process' is the main one with CPU count. And
overall the query just take very long than expected to finish. We could
have very normal days for a while and randomly we will encountered the
symptom above. Each time query we ran is different. Some are light and
some are heavy. The query we recently ran is to create index on many big
partitioned tables.
Thanks in advance.Hi,
Thanks for your post!
From your description, I understand that:
You installed SQL Server 2005 64bit as clustered servers. The Windows
Server 2003 was also 64bit.
When you executed the query in SQL Server Management Studio locally, the
remote desktop need a long time to connect the server and had very slow
responses for requests.
At that time, extra connections to the SQL Server were also slow and even
failed. The CPU use ratio and disk I/O level were low then.
This happened once a while. The latest query was to create index on many
large partitioned tables.
If I have misunderstood, please let me know.
To narrow down this issue, I appreciate you send me the performance report
files of SQL Server Profiler and Performance Monitor when the issue is
happening again.
Also, I appreciate you could tell me more about the query when this issue
happened:
What's kind of the query?
Does a simple query will cause that problem?
When this issue happened, what were recorded in the SQL error logs and
system event logs?
If you have any other concerns, please feel free to let me know. It's my
pleasure to be of assistance.
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others:
https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
Thanks for your post!
From your description, I understand that:
You installed SQL Server 2005 64bit as clustered servers. The Windows
Server 2003 was also 64bit.
When you executed the query in SQL Server Management Studio locally, the
remote desktop need a long time to connect the server and had very slow
responses for requests.
At that time, extra connections to the SQL Server were also slow and even
failed. The CPU use ratio and disk I/O level were low then.
This happened once a while. The latest query was to create index on many
large partitioned tables.
If I have misunderstood, please let me know.
To narrow down this issue, I appreciate you send me the performance report
files of SQL Server Profiler and Performance Monitor when the issue is
happening again.
Also, I appreciate you could tell me more about the query when this issue
happened:
What's kind of the query?
Does a simple query will cause that problem?
When this issue happened, what were recorded in the SQL error logs and
system event logs?
If you have any other concerns, please feel free to let me know. It's my
pleasure to be of assistance.
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others:
https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||American are in vacation "Independency Day".
Charles|||Hi ,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Have a great day!
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=====================
This posting is provided "AS IS" with no warranties, and confers no rights.
We are moving from SQL Server 2000 to SQL Server 2005. Wondering if you
have the same problem or can share some of your experience to solve some
issue.
We have brand new clustered database servers which have Quad Duo CPUs
and 8GB memory each running SQL Server 2005 64bit Enterprise edition
with SP1 applied and on Win2K3 server x64 Enterprise edition. Only
database engine component are installed for now.
We have some strange behaviors from time to time that :
When we have some query running from some local PC Management Studio,
the remote desktop control is either takes a long time to connect or
very slow to react the requests. When it happened, sometime we can not
connect to the database server from Management Studio with the
connection timeout which was OK couple minutes ago. Interestingly at
those moments, the query is still running , no process blocking found,
very light CPU usage and low disk I/O detected from the monitoring tool
and it looks like the SQL server is not busy at all. No 3rd
party/identifiable process can be found running on the database server
also, the 'system idle process' is the main one with CPU count. And
overall the query just take very long than expected to finish. We could
have very normal days for a while and randomly we will encountered the
symptom above. Each time query we ran is different. Some are light and
some are heavy. The query we recently ran is to create index on many big
partitioned tables.
Thanks in advance.Hi,
Thanks for your post!
From your description, I understand that:
You installed SQL Server 2005 64bit as clustered servers. The Windows
Server 2003 was also 64bit.
When you executed the query in SQL Server Management Studio locally, the
remote desktop need a long time to connect the server and had very slow
responses for requests.
At that time, extra connections to the SQL Server were also slow and even
failed. The CPU use ratio and disk I/O level were low then.
This happened once a while. The latest query was to create index on many
large partitioned tables.
If I have misunderstood, please let me know.
To narrow down this issue, I appreciate you send me the performance report
files of SQL Server Profiler and Performance Monitor when the issue is
happening again.
Also, I appreciate you could tell me more about the query when this issue
happened:
What's kind of the query?
Does a simple query will cause that problem?
When this issue happened, what were recorded in the SQL error logs and
system event logs?
If you have any other concerns, please feel free to let me know. It's my
pleasure to be of assistance.
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others:
https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
Thanks for your post!
From your description, I understand that:
You installed SQL Server 2005 64bit as clustered servers. The Windows
Server 2003 was also 64bit.
When you executed the query in SQL Server Management Studio locally, the
remote desktop need a long time to connect the server and had very slow
responses for requests.
At that time, extra connections to the SQL Server were also slow and even
failed. The CPU use ratio and disk I/O level were low then.
This happened once a while. The latest query was to create index on many
large partitioned tables.
If I have misunderstood, please let me know.
To narrow down this issue, I appreciate you send me the performance report
files of SQL Server Profiler and Performance Monitor when the issue is
happening again.
Also, I appreciate you could tell me more about the query when this issue
happened:
What's kind of the query?
Does a simple query will cause that problem?
When this issue happened, what were recorded in the SQL error logs and
system event logs?
If you have any other concerns, please feel free to let me know. It's my
pleasure to be of assistance.
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others:
https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||American are in vacation "Independency Day".
Charles|||Hi ,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Have a great day!
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=====================
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to:
Posts (Atom)