Query Performance

I am attempting to get a better understanding of why my SQL 2005 setup when running a simple select statement on a large table is displaying very low IO in performance monitor. If i run a single Select * From testtable i see 4mb\sec transfer and Disk reads\sec is around 8-9. This particular table is sitting on a single U320 10k drive so i expecting to see far more substantial IO. Does anyone have any information on how IO is consumed using different SQL operations so i can obtain a better understanding?

Flush the cache. Some, and possibly a lot, of data may be cached in memory.

Also, since a select query does not require any transaction logging or updating of indexes or statistics, a select query would have substantially less IO than an insert, update, or delete query.

Flush the cache and then run: Insert Into testtable Select * From testtable Order By SomeNonClusteredField

|||

hi

I did flush the cache prior to running this statement. Why doent SQL use the I\O to its full potential if that is the only process running on the box? I was expecting to see the I\O near to saturated.

Query over many to many relationship

I have 2 tables with many to many relatioship

tblTitle and tblStyle

I have setup a connecting table tblTitleStyle which contains 2 forein keys TitleID and StyleID to connect the 2 tables.

I can query 2 tables with one to many relatioship using JOINT statement. How do I implement the JOINT statement across tblTitleStyle though? Or is it a wrong aproach altogether, should I use maybe a subquery?

Thanks, Jakub

Does the code below help you out?

Chris

SELECT <insert column names>

FROM dbo.tblTitleStyle ts

INNER JOIN dbo.tblTitle t ON t.TitleID = ts.TitleID

INNER JOIN dbo.tblStyle s ON s.StyleID = ts.StyleID

|||

Wow...that was quick and shure works like a charm!..greatly appreciated.

Now I have in adition 2 more tables (names are slightly different...I am in the process of changing them)

Composer and Category

Here is the complete DB scenario:

Title

with many to one relationship to

Composer

Category

and many to many relationship to

Genre (Style before)

I can use one query to SELECT data from Title, Composer and Category:

SELECT t.Title, c.Category, co.Composer

FROM Title t

INNER JOIN Composer co ON t.Index_Composer = co.Index_Composer

INNER JOIN Category c ON t.Index_Category = c.Index_Category

and one query (thanks to you) from the Genre table:

SELECT t.Title, g.Genre
FROM dbo.TitleGenre tg
INNER JOIN dbo.Title t ON t.Index_Title = tg.Index_Title
INNER JOIN dbo.Genre g ON g.Index_Genre = tg.Index_Genre

Can I combine those 2 queries into one to get the result in one form? The problem is that FROM calls for different tables in those 2 queries.

Thanks again...Jakub

I

|||

This should do the trick....

Chris

SELECT t.Title, c.Category, co.Composer, g.genre

FROM Title t

INNER JOIN Composer co ON t.Index_Composer = co.Index_Composer

INNER JOIN Category c ON t.Index_Category = c.Index_Category

INNER JOIN dbo.TitleGenre tg ON tg.TitleID = t.TitleID

INNER JOIN dbo.Genre g ON g.Index_Genre = tg.Index_Genre

|||

It did, thanks... the query builder in VS web dev produced this convoluted code which didn't work.

Check out www.concertantechamber players.com in few days to see the results...

query on day's transactions

Let me preface this by saying that i'm not a programmer.
I'm trying to setup a job that runs a query every night at 7pm that will
pull all transactions with the date reported that equals the system date.
What identifier would I use so that it pulled transactions based on the the
system date? My query is below, but as you see it has a yesterday's date.
select [CaseLog].[DateReported], [CaseLog].[CaseNbr]
from [CaseLog]
where [CaseLog].[DateReported]='2005-09-26 00:00:00'
order by [CaseLog].[DateReported], [CaseLog].[CaseNbr]
Thanks in advance
On Mon, 26 Sep 2005 13:45:03 -0700, dhamric wrote:

>Let me preface this by saying that i'm not a programmer.
>I'm trying to setup a job that runs a query every night at 7pm that will
>pull all transactions with the date reported that equals the system date.
>What identifier would I use so that it pulled transactions based on the the
>system date? My query is below, but as you see it has a yesterday's date.
>select [CaseLog].[DateReported], [CaseLog].[CaseNbr]
>from [CaseLog]
>where [CaseLog].[DateReported]='2005-09-26 00:00:00'
>order by [CaseLog].[DateReported], [CaseLog].[CaseNbr]
>Thanks in advance
Hi dhamric,
You could use
SELECT CaseLog.DateReported, CaseLog.CaseNbr
FROM CaseLog
WHERE CaseLog.DateReported = DATEADD(day,
DATEDIFF(day, 0, getdate()), 0)
ORDER BY CaseLog.DateReported, CaseLog.CaseNbr
But if the DateReported column can be stored with a time portion other
than midnight, you should use this instead:
SELECT CaseLog.DateReported, CaseLog.CaseNbr
FROM CaseLog
WHERE CaseLog.DateReported >= DATEADD(day,
DATEDIFF(day, 0, getdate()), 0)
AND CaseLog.DateReported < DATEADD(day,
DATEDIFF(day, 0, getdate()), 1)
ORDER BY CaseLog.DateReported, CaseLog.CaseNbr
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Very helpful. Thank you!
"Hugo Kornelis" wrote:

> On Mon, 26 Sep 2005 13:45:03 -0700, dhamric wrote:
>
> Hi dhamric,
> You could use
> SELECT CaseLog.DateReported, CaseLog.CaseNbr
> FROM CaseLog
> WHERE CaseLog.DateReported = DATEADD(day,
> DATEDIFF(day, 0, getdate()), 0)
> ORDER BY CaseLog.DateReported, CaseLog.CaseNbr
> But if the DateReported column can be stored with a time portion other
> than midnight, you should use this instead:
> SELECT CaseLog.DateReported, CaseLog.CaseNbr
> FROM CaseLog
> WHERE CaseLog.DateReported >= DATEADD(day,
> DATEDIFF(day, 0, getdate()), 0)
> AND CaseLog.DateReported < DATEADD(day,
> DATEDIFF(day, 0, getdate()), 1)
> ORDER BY CaseLog.DateReported, CaseLog.CaseNbr
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Query notification problem

I've got an application that's using query notifications with a two-user setup (a user that starts the dependency running, and an application user that subscribes to it). I'm having problems getting the query notifications to go through however... according to the Profiler, when the application user does something that triggers a query notification, it causes this error:

This message could not be delivered because the user with ID [application user] in database ID [database] does not have permission to send to the service. Service name: SqlQueryNotificationService-[guid].

The problem is that [guid] is different each time, and there doesn't seem to be a generic "grant send on all services" statement, so I can't grant send on the particular service in question to the application user to get rid of the error. (If I grant db_owner to the application user, the problem goes away and everything works, but I'd rather not do that. The application user already has subscribe query notifications, receive on QueryNotificationErrorsQueue, and references on contract::...PostQueryNotification.)

Obviously I'm doing something wrong, but I am completely out of ideas and can't seem to find anything new to try. Any ideas? Thanks.

It is not clear to me how can an application separate the roles of creating the notification and getting the notification using SqlDependency (which is the component that creates the 'SqlQueryNotificationService-[guid]' named services). The SqlDependency is intended to be used by one appdomain that calls Start and then various callbacks (notification subscriptions) are being set up by the same appdomain.

Do you have a particular reason why you want to separate the two roles?

Utimately you can always go one level down and use the SqlNotification objects instead of SqlDependency. They are more primitive (no automated provisioning of a temp service/queue/procedure for each appdomain etc etc), but they allow more control as you can specify explicitly the service that is intended to receive the notifications and you can set up said service as your heart desires (including all the necessary GRANTs).

|||The reason for separating them is that the user starting the dependency has to have a bunch of create privileges in its default schema, and for security reasons we don't want the application user to have such access. By isolating the user doing the start in its own schema, we can keep the application user and the main schema locked down.

It turns out that there is a solution, however... by Reflecting through the SqlDependency class, I determined that it's hard-wired to look for a role called "sql_dependency_subscriber", and if that role exists it grants the necessary privileges to it upon startup. By creating the role and adding the application user to it, everything works. Seems like a major kludge on Microsoft's part to me, and it would have helped if it was documented somewhere, but it does the trick nonetheless.
|||Well, that is news for me as well. I never knew SqlDependency does that.

Query Notification - Setup Problems

Hi Guys,

I'm having some problems setting up a SQL Server 2005 Query Notification application. I've only recently moved from SQL Server 2000 so 2005 is still a little alien to me!

I've followed example found on the internet in the following steps:

1.

ALTER DATABASE <dbname> SET ENABLE_BROKER

2.

GRANT CREATE PROCEDURE TO <user>

GRANT CREATE QUEUE TO <user>

GRANT CREATE SERVICE TO <user>

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO <user>

GRANT RECEIVE ON QueryNotificationErrorsQueue TO <user>

3.

ALTER DATABASE <dbname> SET TRUSTWORTHY ON

The application developers have also followed the same example setting up SQL cache and the connection code. When they start the application I can see the connection in the activity monitor but it has a status of SUSPENDED. I've also checked the view:

SELECT * FROM sys.dm_qn_subscriptions

There are no subscriptions setup I can however see that there is a new SP created. I tried to creating master keys as well but I'm not sure what the reasoning behind this is.

Is there anything else I need to take into consideration or check? When I change the data which they use for their select statement SQL profiler doesn't register anything (except the update). I have however seen this:

exec sp_executesql N'END CONVERSATION @.p1; BEGIN CONVERSATION TIMER (''69e2c786-9d33-dc11-a751-0050568146e8'') TIMEOUT = 120; WAITFOR(RECEIVE TOP (1) message_type_name,
conversation_handle, cast(message_body AS XML) as message_body from [SqlQueryNotificationService-5fb25d04-6ef6-47e6-9406-854f15e16eb7]), TIMEOUT @.p2;',N'@.p2 int,@.p1
uniqueidentifier',@.p2=60000,@.p1='BD98F6B4-A133-DC11-A751-0050568146E8'

This occurs when they refresh the page.

Any ideas?

Cheers!

Ian

You should place the SqlDependency.Start in the appdomain load event. From your description it seems to be placed on the page load.