data caching on SQL Server?

Suppose 40 winform clients of SQL Server(SS) 2005 request the same data (select * from sometable).

Is there any proxy service in SS or all clients will be queueing/competing for the same answer?

Hi,

I think its the dot net that do the caching not Sql server.

http://www.codeproject.com/csharp/webservicecache.asp

regards,

joey

|||

Different winform clients are on different machines with different .NET frameworks generally without any knowledge about each other connecting to one central MS SQL Server db?

So, each winform client may cache data to avoid repeting the request to SQL Server... according to your link. Or it is done by ASP.NET/IIS (for webforms)

But the question is about caching by SQL Server, to avoid the server to service the same results to different WINFORM clients.

Since SQL Server 2005 is integrated with .NET, it is quite logical to expect such proxy service from SQL Server or .NET framework whith which MSS2005 is integrated with, isn't it?

Is it available by MS SQL Server 2005? how it is called? or I should create it?

|||

hi,

you might want to create a 'view' or an 'indexed view'

if the query is not parameterized or a stored procedure if it is parameterized

if you're into a very fast performance i'll recommend index view though you

will be penalized for disk space.

if its is a stored procedure execution is cached not the the result .

the link i gave you was caching the data by having winforms to consume

a webservice which stores the cached dataset

regards,

joey

|||

joeydj , Thanks for your guidance

Having rephrased my doubt more concisely:
if ASP.NET server data webcontrols permit declarative specification for caching data (on server side), I do not see why winforms controls have not the ability to do the same, say, through one of MS SQL Server2005 configurable and built.in (web)sevice...

|||

In my question I really wanted to ask whether the MSSSdb engine needs to compute/execute data according query again (even according to compiled/cached plan) and whether it repeats I/O operations in order to to get the same data structures.

This is rather important to know in order to make decisions on where to customly cache (on server or client), when, for how long and how much data.

Reporting Services has cached reports.
Really(and obviously) there are data buffers in memory. Also one can explicitly place data structures into memory [1]

Code Snippet

--Use the pubs database USE pubs DECLARE @.dbid INTEGER SET @.dbid = DB_ID('pubs') --Determine id number for the dbo.authorstable DECLARE @.obid INTEGER SET @.obid = OBJECT_ID('dbo.authors') --Pin the dbo.authors table to memory DBCC PINTABLE (@.dbid,@.obid) GO

Though details how to control memory management are not very explicit in docs

[1]
Introduction into Caching in SQL Server 2000

http://www.extremeexperts.com/SQL/Articles/SQLCacheObjects.aspx

|||

Well, after all there is direct match to my question.

ADO.NET 2.0 (SQL Server 2005) supports

1)
server-side cursors

ExecuteResultSet of SqlResultSet

2)

Paging

ExecutePageReader(CommandBehavior.Default, nStartRow, nPageSize);

3)

Asynchronous execution of commands

4)

MARS - Multiple Result Sets

Data base mirroring fail over clients redirects

Hello,
I have recently installed and configured SQL 2005 SE with database mirroring
configured with high safety with automatic failover synchronous mode.
My question is (I probably missed the principle idea) in case of failover
occurred how do the clients redirect to the second node transparently.
Thanks in advanced.
Tal shalom
You must specify the initial principal server and database in the
connection string and the failover partner server.
Data Source=myServerAddress;Failover Partner=myMirrorServer;Initial
Catalog=myDataBase;Integrated Security=True;
"Tal Bar-Or" <TalBarOr@.discussions.microsoft.com> wrote in message
news:65B28837-DFDB-45A6-858E-F73D291E86F1@.microsoft.com...
> Hello,
> I have recently installed and configured SQL 2005 SE with database
> mirroring
> configured with high safety with automatic failover synchronous mode.
> My question is (I probably missed the principle idea) in case of failover
> occurred how do the clients redirect to the second node transparently.
> Thanks in advanced.
|||Shalom Uri
Thanks for the answer, will it be the right option to use also Microsoft SQL
Server Native Client and choosing mirror server will it achieve the same
results.
Thanks
"Uri Dimant" wrote:

> Tal shalom
> You must specify the initial principal server and database in the
> connection string and the failover partner server.
> Data Source=myServerAddress;Failover Partner=myMirrorServer;Initial
> Catalog=myDataBase;Integrated Security=True;
>
> "Tal Bar-Or" <TalBarOr@.discussions.microsoft.com> wrote in message
> news:65B28837-DFDB-45A6-858E-F73D291E86F1@.microsoft.com...
>
>
|||Hi Tal
Yes, I forgot to mention that you have to use ADO.NET or the SQL Native
Client .
"Tal Bar-Or" <TalBarOr@.discussions.microsoft.com> wrote in message
news:26F6792F-0750-4E87-B438-A602908DA166@.microsoft.com...[vbcol=seagreen]
> Shalom Uri
> Thanks for the answer, will it be the right option to use also Microsoft
> SQL
> Server Native Client and choosing mirror server will it achieve the same
> results.
> Thanks
>
> "Uri Dimant" wrote:
|||thanks
cheers
"Uri Dimant" wrote:

> Hi Tal
> Yes, I forgot to mention that you have to use ADO.NET or the SQL Native
> Client .
>
>
> "Tal Bar-Or" <TalBarOr@.discussions.microsoft.com> wrote in message
> news:26F6792F-0750-4E87-B438-A602908DA166@.microsoft.com...
>
>

Data and log files on separate disks

The production database of one of our clients is set up with both the data
and log files on the same disk. We suggested that they should separate them
out to eliminate disk contention and to eliminate I/O problems. The DBA at
the client site came back saying that he does not believe this is necessary.
Are we wrong in suggesting that the data and log files should be on separate
disks?
The client database is about 20 GB.
Thanks in advance.
"Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
news:80F4426D-0326-40D5-9EF4-B42853066EE6@.microsoft.com...
> The production database of one of our clients is set up with both the data
> and log files on the same disk. We suggested that they should separate
> them
> out to eliminate disk contention and to eliminate I/O problems. The DBA at
> the client site came back saying that he does not believe this is
> necessary.
> Are we wrong in suggesting that the data and log files should be on
> separate
> disks?
No, you're not.
However, whether it actually helps their performance is a good question.
If it's small enough and low volume enough, it won't help there.
If say the machine can only support 2 disks in a RAID 1 config, this
solution is "ok" given the machine.
If the machine can support more RAIDs (say 4 disks) then they're probably
better off separating them just because then if two disks fail, they're less
likely to actually suffer data loss.

> The client database is about 20 GB.
> Thanks in advance.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Thanks for the reply. As I mentioned the database is about 20GB and there is
plenty of activity during office hours and during other scheduled task runs.
Do you still believe separating the data and log files will improve some of
the bottlenecks.
Thanks
"Greg D. Moore (Strider)" wrote:

> "Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
> news:80F4426D-0326-40D5-9EF4-B42853066EE6@.microsoft.com...
> No, you're not.
> However, whether it actually helps their performance is a good question.
> If it's small enough and low volume enough, it won't help there.
> If say the machine can only support 2 disks in a RAID 1 config, this
> solution is "ok" given the machine.
> If the machine can support more RAIDs (say 4 disks) then they're probably
> better off separating them just because then if two disks fail, they're less
> likely to actually suffer data loss.
>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>
>
|||"Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
news:9ABCC48E-C789-482F-8F42-D02AB663E307@.microsoft.com...
> Thanks for the reply. As I mentioned the database is about 20GB and there
> is
> plenty of activity during office hours and during other scheduled task
> runs.
> Do you still believe separating the data and log files will improve some
> of
> the bottlenecks.
Is it experiencing disk I/O bottlenecks?
If so, it would probably help. But again. 20GB and 'plenty of activity'
doesn't really tell enough to say for sure.
[vbcol=seagreen]
> Thanks
>
> "Greg D. Moore (Strider)" wrote:
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Frank1213 wrote:
> Thanks for the reply. As I mentioned the database is about 20GB and there is
> plenty of activity during office hours and during other scheduled task runs.
> Do you still believe separating the data and log files will improve some of
> the bottlenecks.
> Thanks
>
Hi,
Maybe you should ask is the disk is in their SAN. It's true that in most
cases it's good practice to have your database and logfiles on seperate
disk - both for performance reasons but also for disaster recovery
reasons. There are a few cases where this doesn't really make any
difference though. If they e.g. are using a HP EVA SAN there isn't any
performance reason to have the files on different disks because in the
end these disk will share the same physical spindles. The EVA SAN simply
puts all the disks in one big diskgroup and then on top of that you can
create your virtual disks. This means that if you create 2 virtual disks
and present to a server, the server will see it as 2 disks. In the end
it's not any different than just creating 1 virtual disk and put
everything on that single virtual disk - it's still the same physical
spindles it puts the data on.
If this is the case with your customer that could be the reason for why
the DBA answers like he do.
Regards
Steen Schlter Persson
Database Administrator / System Administrator
|||Thanks to both of you for your answers.
""Steen Schlüter Persson (DK)"" wrote:

> Frank1213 wrote:
> Hi,
> Maybe you should ask is the disk is in their SAN. It's true that in most
> cases it's good practice to have your database and logfiles on seperate
> disk - both for performance reasons but also for disaster recovery
> reasons. There are a few cases where this doesn't really make any
> difference though. If they e.g. are using a HP EVA SAN there isn't any
> performance reason to have the files on different disks because in the
> end these disk will share the same physical spindles. The EVA SAN simply
> puts all the disks in one big diskgroup and then on top of that you can
> create your virtual disks. This means that if you create 2 virtual disks
> and present to a server, the server will see it as 2 disks. In the end
> it's not any different than just creating 1 virtual disk and put
> everything on that single virtual disk - it's still the same physical
> spindles it puts the data on.
> If this is the case with your customer that could be the reason for why
> the DBA answers like he do.
> --
> Regards
> Steen Schlüter Persson
> Database Administrator / System Administrator
>

Data and log files on separate disks

The production database of one of our clients is set up with both the data
and log files on the same disk. We suggested that they should separate them
out to eliminate disk contention and to eliminate I/O problems. The DBA at
the client site came back saying that he does not believe this is necessary.
Are we wrong in suggesting that the data and log files should be on separate
disks?
The client database is about 20 GB.
Thanks in advance."Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
news:80F4426D-0326-40D5-9EF4-B42853066EE6@.microsoft.com...
> The production database of one of our clients is set up with both the data
> and log files on the same disk. We suggested that they should separate
> them
> out to eliminate disk contention and to eliminate I/O problems. The DBA at
> the client site came back saying that he does not believe this is
> necessary.
> Are we wrong in suggesting that the data and log files should be on
> separate
> disks?
No, you're not.
However, whether it actually helps their performance is a good question.
If it's small enough and low volume enough, it won't help there.
If say the machine can only support 2 disks in a RAID 1 config, this
solution is "ok" given the machine.
If the machine can support more RAIDs (say 4 disks) then they're probably
better off separating them just because then if two disks fail, they're less
likely to actually suffer data loss.
> The client database is about 20 GB.
> Thanks in advance.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Thanks for the reply. As I mentioned the database is about 20GB and there is
plenty of activity during office hours and during other scheduled task runs.
Do you still believe separating the data and log files will improve some of
the bottlenecks.
Thanks
"Greg D. Moore (Strider)" wrote:
> "Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
> news:80F4426D-0326-40D5-9EF4-B42853066EE6@.microsoft.com...
> > The production database of one of our clients is set up with both the data
> > and log files on the same disk. We suggested that they should separate
> > them
> > out to eliminate disk contention and to eliminate I/O problems. The DBA at
> > the client site came back saying that he does not believe this is
> > necessary.
> > Are we wrong in suggesting that the data and log files should be on
> > separate
> > disks?
> No, you're not.
> However, whether it actually helps their performance is a good question.
> If it's small enough and low volume enough, it won't help there.
> If say the machine can only support 2 disks in a RAID 1 config, this
> solution is "ok" given the machine.
> If the machine can support more RAIDs (say 4 disks) then they're probably
> better off separating them just because then if two disks fail, they're less
> likely to actually suffer data loss.
>
> > The client database is about 20 GB.
> > Thanks in advance.
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>
>|||"Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
news:9ABCC48E-C789-482F-8F42-D02AB663E307@.microsoft.com...
> Thanks for the reply. As I mentioned the database is about 20GB and there
> is
> plenty of activity during office hours and during other scheduled task
> runs.
> Do you still believe separating the data and log files will improve some
> of
> the bottlenecks.
Is it experiencing disk I/O bottlenecks?
If so, it would probably help. But again. 20GB and 'plenty of activity'
doesn't really tell enough to say for sure.
> Thanks
>
> "Greg D. Moore (Strider)" wrote:
>> "Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
>> news:80F4426D-0326-40D5-9EF4-B42853066EE6@.microsoft.com...
>> > The production database of one of our clients is set up with both the
>> > data
>> > and log files on the same disk. We suggested that they should separate
>> > them
>> > out to eliminate disk contention and to eliminate I/O problems. The DBA
>> > at
>> > the client site came back saying that he does not believe this is
>> > necessary.
>> > Are we wrong in suggesting that the data and log files should be on
>> > separate
>> > disks?
>> No, you're not.
>> However, whether it actually helps their performance is a good question.
>> If it's small enough and low volume enough, it won't help there.
>> If say the machine can only support 2 disks in a RAID 1 config, this
>> solution is "ok" given the machine.
>> If the machine can support more RAIDs (say 4 disks) then they're probably
>> better off separating them just because then if two disks fail, they're
>> less
>> likely to actually suffer data loss.
>>
>> > The client database is about 20 GB.
>> > Thanks in advance.
>>
>> --
>> Greg Moore
>> SQL Server DBA Consulting Remote and Onsite available!
>> Email: sql (at) greenms.com
>> http://www.greenms.com/sqlserver.html
>>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Frank1213 wrote:
> Thanks for the reply. As I mentioned the database is about 20GB and there is
> plenty of activity during office hours and during other scheduled task runs.
> Do you still believe separating the data and log files will improve some of
> the bottlenecks.
> Thanks
>
Hi,
Maybe you should ask is the disk is in their SAN. It's true that in most
cases it's good practice to have your database and logfiles on seperate
disk - both for performance reasons but also for disaster recovery
reasons. There are a few cases where this doesn't really make any
difference though. If they e.g. are using a HP EVA SAN there isn't any
performance reason to have the files on different disks because in the
end these disk will share the same physical spindles. The EVA SAN simply
puts all the disks in one big diskgroup and then on top of that you can
create your virtual disks. This means that if you create 2 virtual disks
and present to a server, the server will see it as 2 disks. In the end
it's not any different than just creating 1 virtual disk and put
everything on that single virtual disk - it's still the same physical
spindles it puts the data on.
If this is the case with your customer that could be the reason for why
the DBA answers like he do.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||Thanks to both of you for your answers.
""Steen Schlüter Persson (DK)"" wrote:
> Frank1213 wrote:
> > Thanks for the reply. As I mentioned the database is about 20GB and there is
> > plenty of activity during office hours and during other scheduled task runs.
> > Do you still believe separating the data and log files will improve some of
> > the bottlenecks.
> > Thanks
> >
> >
> Hi,
> Maybe you should ask is the disk is in their SAN. It's true that in most
> cases it's good practice to have your database and logfiles on seperate
> disk - both for performance reasons but also for disaster recovery
> reasons. There are a few cases where this doesn't really make any
> difference though. If they e.g. are using a HP EVA SAN there isn't any
> performance reason to have the files on different disks because in the
> end these disk will share the same physical spindles. The EVA SAN simply
> puts all the disks in one big diskgroup and then on top of that you can
> create your virtual disks. This means that if you create 2 virtual disks
> and present to a server, the server will see it as 2 disks. In the end
> it's not any different than just creating 1 virtual disk and put
> everything on that single virtual disk - it's still the same physical
> spindles it puts the data on.
> If this is the case with your customer that could be the reason for why
> the DBA answers like he do.
> --
> Regards
> Steen Schlüter Persson
> Database Administrator / System Administrator
>

Data and log files on separate disks

The production database of one of our clients is set up with both the data
and log files on the same disk. We suggested that they should separate them
out to eliminate disk contention and to eliminate I/O problems. The DBA at
the client site came back saying that he does not believe this is necessary.
Are we wrong in suggesting that the data and log files should be on separate
disks?
The client database is about 20 GB.
Thanks in advance."Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
news:80F4426D-0326-40D5-9EF4-B42853066EE6@.microsoft.com...
> The production database of one of our clients is set up with both the data
> and log files on the same disk. We suggested that they should separate
> them
> out to eliminate disk contention and to eliminate I/O problems. The DBA at
> the client site came back saying that he does not believe this is
> necessary.
> Are we wrong in suggesting that the data and log files should be on
> separate
> disks?
No, you're not.
However, whether it actually helps their performance is a good question.
If it's small enough and low volume enough, it won't help there.
If say the machine can only support 2 disks in a RAID 1 config, this
solution is "ok" given the machine.
If the machine can support more RAIDs (say 4 disks) then they're probably
better off separating them just because then if two disks fail, they're less
likely to actually suffer data loss.

> The client database is about 20 GB.
> Thanks in advance.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Thanks for the reply. As I mentioned the database is about 20GB and there is
plenty of activity during office hours and during other scheduled task runs.
Do you still believe separating the data and log files will improve some of
the bottlenecks.
Thanks
"Greg D. Moore (Strider)" wrote:

> "Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
> news:80F4426D-0326-40D5-9EF4-B42853066EE6@.microsoft.com...
> No, you're not.
> However, whether it actually helps their performance is a good question.
> If it's small enough and low volume enough, it won't help there.
> If say the machine can only support 2 disks in a RAID 1 config, this
> solution is "ok" given the machine.
> If the machine can support more RAIDs (say 4 disks) then they're probably
> better off separating them just because then if two disks fail, they're le
ss
> likely to actually suffer data loss.
>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com [url]http://www.greenms.com/sqlserver.html[/ur
l]
>
>|||"Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
news:9ABCC48E-C789-482F-8F42-D02AB663E307@.microsoft.com...
> Thanks for the reply. As I mentioned the database is about 20GB and there
> is
> plenty of activity during office hours and during other scheduled task
> runs.
> Do you still believe separating the data and log files will improve some
> of
> the bottlenecks.
Is it experiencing disk I/O bottlenecks?
If so, it would probably help. But again. 20GB and 'plenty of activity'
doesn't really tell enough to say for sure.
[vbcol=seagreen]
> Thanks
>
> "Greg D. Moore (Strider)" wrote:
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Frank1213 wrote:
> Thanks for the reply. As I mentioned the database is about 20GB and there
is
> plenty of activity during office hours and during other scheduled task run
s.
> Do you still believe separating the data and log files will improve some o
f
> the bottlenecks.
> Thanks
>
Hi,
Maybe you should ask is the disk is in their SAN. It's true that in most
cases it's good practice to have your database and logfiles on seperate
disk - both for performance reasons but also for disaster recovery
reasons. There are a few cases where this doesn't really make any
difference though. If they e.g. are using a HP EVA SAN there isn't any
performance reason to have the files on different disks because in the
end these disk will share the same physical spindles. The EVA SAN simply
puts all the disks in one big diskgroup and then on top of that you can
create your virtual disks. This means that if you create 2 virtual disks
and present to a server, the server will see it as 2 disks. In the end
it's not any different than just creating 1 virtual disk and put
everything on that single virtual disk - it's still the same physical
spindles it puts the data on.
If this is the case with your customer that could be the reason for why
the DBA answers like he do.
Regards
Steen Schlter Persson
Database Administrator / System Administrator|||Thanks to both of you for your answers.
""Steen Schlüter Persson (DK)"" wrote:

> Frank1213 wrote:
> Hi,
> Maybe you should ask is the disk is in their SAN. It's true that in most
> cases it's good practice to have your database and logfiles on seperate
> disk - both for performance reasons but also for disaster recovery
> reasons. There are a few cases where this doesn't really make any
> difference though. If they e.g. are using a HP EVA SAN there isn't any
> performance reason to have the files on different disks because in the
> end these disk will share the same physical spindles. The EVA SAN simply
> puts all the disks in one big diskgroup and then on top of that you can
> create your virtual disks. This means that if you create 2 virtual disks
> and present to a server, the server will see it as 2 disks. In the end
> it's not any different than just creating 1 virtual disk and put
> everything on that single virtual disk - it's still the same physical
> spindles it puts the data on.
> If this is the case with your customer that could be the reason for why
> the DBA answers like he do.
> --
> Regards
> Steen Schlüter Persson
> Database Administrator / System Administrator
>

Cycling Through Subreports

I have a master report which contains several subreports. My end users want these reports to cycle through for a list of clients in a loop. So, for example, given a list of three clients, the users want to see all subreports run for client 1, then all subreports run for client 2, then all subreports run for client3. (I hope this is making sense)

Currently the report uses an "in" statement so the users get subreport 1 for clients 1, 2, and 3, followed by subreport 2 for clients 1, 2, and 3, etc., which is not what they want.

I've tried placing the subreports inside a table and creating a group, but that just seems to crash the Report Designer.

Is there any to create a loop outside of the report that will call the report once for each client? Or is there some other way I can get it to run the subreports one client at a time?

Any help would be greatly appreciated.

Creating a group by client and putting the subreports inside of the group should be the right way of doing it. I'm not sure why the designer crashes when you are doing this using a table. But you can also try using a list instead.

CXPACKET Wait Issue

I was monitoring a client's warehouse build process last night and stumbled
across the following. During a sproc call that moves data from a staging db
to warehouse db, the spid parallelized on all 8 cpus and then sat there with
the following information shown by sys.dm_os_waiting_tasks. Note that all
CPUs were pegged at 100% during this event, although I don't know if this
particular proc was the cause. To my knowledge this was the only activity
on the server at the time however. There wasn't really any other waiting
tasks of note other than the typical system tasks. I was unable to get more
detailed information on the actual blocking tasks tho. It was pretty late
in the night and I wasn't my sharpest.
This is a 2005 box, patched up past SP2 to build 3186. 64bit, 32GB RAM, 4
dual core sprocs no hyperthreading. The I/O subsystem isn't very beefy (7
drive RAID5 set on a middling SAN) and the database totals about 400GB or
so.
There were 8 rows in waiting tasks with the below data:
waiting_task_address: 0x0000000000EDA868
session_id: 57
wait_duration_ms: 193860 (at the time of the DMV grab)
wait_type: CXPACKET
resource_address: 0x00000000801F9A50
blocking_task_address: all were different
blocking_session_id: 57
blocking_exec_context_id: all were different
resource_description: exchangeEvent id=port801f61c0 nodeId=5
Anyone got any ideas what an "exchangeEvent id=port801f61c0 nodeId=5"
resource_description is or means? Is it simply an internal mechanism
related to parallelization and perhaps this query (it was either a huge
insert or update - not sure which, sorry) could be sped up (or at least not
get hung up for an extended period) by specifying a MAXDOP of 1, 2 or maybe
4? Any information or help would be appreciated.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
Hi
Have you checked your missing indexes for anything related to the task?
John
"TheSQLGuru" wrote:

> I was monitoring a client's warehouse build process last night and stumbled
> across the following. During a sproc call that moves data from a staging db
> to warehouse db, the spid parallelized on all 8 cpus and then sat there with
> the following information shown by sys.dm_os_waiting_tasks. Note that all
> CPUs were pegged at 100% during this event, although I don't know if this
> particular proc was the cause. To my knowledge this was the only activity
> on the server at the time however. There wasn't really any other waiting
> tasks of note other than the typical system tasks. I was unable to get more
> detailed information on the actual blocking tasks tho. It was pretty late
> in the night and I wasn't my sharpest.
> This is a 2005 box, patched up past SP2 to build 3186. 64bit, 32GB RAM, 4
> dual core sprocs no hyperthreading. The I/O subsystem isn't very beefy (7
> drive RAID5 set on a middling SAN) and the database totals about 400GB or
> so.
>
> There were 8 rows in waiting tasks with the below data:
> waiting_task_address: 0x0000000000EDA868
> session_id: 57
> wait_duration_ms: 193860 (at the time of the DMV grab)
> wait_type: CXPACKET
> resource_address: 0x00000000801F9A50
> blocking_task_address: all were different
> blocking_session_id: 57
> blocking_exec_context_id: all were different
> resource_description: exchangeEvent id=port801f61c0 nodeId=5
>
> Anyone got any ideas what an "exchangeEvent id=port801f61c0 nodeId=5"
> resource_description is or means? Is it simply an internal mechanism
> related to parallelization and perhaps this query (it was either a huge
> insert or update - not sure which, sorry) could be sped up (or at least not
> get hung up for an extended period) by specifying a MAXDOP of 1, 2 or maybe
> 4? Any information or help would be appreciated.
>
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> kgboles a earthlink dt net
>
>
|||Unfortunately the client rebuilds all indexes each night with their
warehouse rebuild so missing indexes isn't useful.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:FCF33C96-720B-4F2C-96BD-2E2A8179CFA5@.microsoft.com...[vbcol=seagreen]
> Hi
> Have you checked your missing indexes for anything related to the task?
> John
> "TheSQLGuru" wrote:
|||Hi
A common cause of parellisation is missing indexes, so unless you can find
the query that is probably causing this you may not be able to solve the
issue. You may want to try SQL profiling the process.
John
"TheSQLGuru" wrote:

> Unfortunately the client rebuilds all indexes each night with their
> warehouse rebuild so missing indexes isn't useful.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> kgboles a earthlink dt net
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:FCF33C96-720B-4F2C-96BD-2E2A8179CFA5@.microsoft.com...
>
>
|||On Dec 24, 8:10Xpm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> A common cause of parellisation is missing indexes, so unless you can find
> the query that is probably causing this you may not be able to solve the
> issue. You may want to try SQL profiling the process.
> John
>
> "TheSQLGuru" wrote:
>
>
>
>
>
>
> - Show quoted text -
Hello,
maybe the reason of the problem are the alter index statements for
rebuilding the indeces.
If that is the case you should run it with specifying a MAXDOP
statement.
|||I was specifically asking about the "exchangeEvent id=port801f61c0 nodeId=5"
resource_description. I am trying to identify what that means or comes from
to see if there is anything I can do to affect it being the wait.
The query that caused this hits the entire table since it is a load
mechanism. Indexing will not help since a tablescan is more efficient in
such cases.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:FF9951B2-2ACE-4A72-8996-53F36082D827@.microsoft.com...[vbcol=seagreen]
> Hi
> A common cause of parellisation is missing indexes, so unless you can find
> the query that is probably causing this you may not be able to solve the
> issue. You may want to try SQL profiling the process.
> John
> "TheSQLGuru" wrote:
|||TheSQLGuru,
It looks like Bart Duncan knows:
http://blogs.msdn.com/bartd/archive/2006/09/25/deadlock-troubleshooting-part-3.aspx
If you read all way down to the end, in his last post Bart makes the
following comment: "You're right -- this is a parallel thread deadlock.
The key indicator of this is the fact that the resources involved in the
deadlock (see the "resource-list" section) are not lock resources; they are
"exchangeEvent" resources, instead."
He goes on to suggest how to catch what is happennig by a profiler trace.
Hope that is some help,
RLF
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13n5da066uv3rce@.corp.supernews.com...
>I was specifically asking about the "exchangeEvent id=port801f61c0
>nodeId=5" resource_description. I am trying to identify what that means or
>comes from to see if there is anything I can do to affect it being the
>wait.
> The query that caused this hits the entire table since it is a load
> mechanism. Indexing will not help since a tablescan is more efficient in
> such cases.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> kgboles a earthlink dt net
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:FF9951B2-2ACE-4A72-8996-53F36082D827@.microsoft.com...
>