RAID 1 and write caching raid controllers

Currently I have a database server that has the log files on a raid array
supported by a controller that does NOT support write caching, only 100%
read. I have another raid controller on the server that does support write
caching.
Would there be any performace benefits from having the raid 1 array with the
log files supported by the raid controller with write caching over one that
does not?
Thanks!!!
yes, as unless there is a rollback or some recovery operation the log is
mostly a write-to file therefore you could reduce some queue time if you put
the log on the write cache controller...but what are you going to put ont he
read caching controller ?
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:A7EEFFFA-A36A-4928-A64C-0A44435CB74A@.microsoft.com...
> Currently I have a database server that has the log files on a raid array
> supported by a controller that does NOT support write caching, only 100%
> read. I have another raid controller on the server that does support write
> caching.
> Would there be any performace benefits from having the raid 1 array with
> the
> log files supported by the raid controller with write caching over one
> that
> does not?
> Thanks!!!
|||Great, thanks for the reply.
Dunno. I suppose nothing.
"David J. Cartwright" wrote:

> yes, as unless there is a rollback or some recovery operation the log is
> mostly a write-to file therefore you could reduce some queue time if you put
> the log on the write cache controller...but what are you going to put ont he
> read caching controller ?
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:A7EEFFFA-A36A-4928-A64C-0A44435CB74A@.microsoft.com...
>
>
|||Is the cache battery backed up and is the machine on a UPS?
SQL does read the log too.
Paul
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:15119B01-2F8D-464D-A287-75D3CFFDBBD3@.microsoft.com...[vbcol=seagreen]
> Great, thanks for the reply.
> Dunno. I suppose nothing.
> "David J. Cartwright" wrote:
sql

RAID 1 and write caching raid controllers

Currently I have a database server that has the log files on a raid array
supported by a controller that does NOT support write caching, only 100%
read. I have another raid controller on the server that does support write
caching.
Would there be any performace benefits from having the raid 1 array with the
log files supported by the raid controller with write caching over one that
does not?
Thanks!!!yes, as unless there is a rollback or some recovery operation the log is
mostly a write-to file therefore you could reduce some queue time if you put
the log on the write cache controller...but what are you going to put ont he
read caching controller ?
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:A7EEFFFA-A36A-4928-A64C-0A44435CB74A@.microsoft.com...
> Currently I have a database server that has the log files on a raid array
> supported by a controller that does NOT support write caching, only 100%
> read. I have another raid controller on the server that does support write
> caching.
> Would there be any performace benefits from having the raid 1 array with
> the
> log files supported by the raid controller with write caching over one
> that
> does not?
> Thanks!!!|||Great, thanks for the reply.
Dunno. I suppose nothing.
"David J. Cartwright" wrote:
> yes, as unless there is a rollback or some recovery operation the log is
> mostly a write-to file therefore you could reduce some queue time if you put
> the log on the write cache controller...but what are you going to put ont he
> read caching controller ?
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:A7EEFFFA-A36A-4928-A64C-0A44435CB74A@.microsoft.com...
> > Currently I have a database server that has the log files on a raid array
> > supported by a controller that does NOT support write caching, only 100%
> > read. I have another raid controller on the server that does support write
> > caching.
> >
> > Would there be any performace benefits from having the raid 1 array with
> > the
> > log files supported by the raid controller with write caching over one
> > that
> > does not?
> >
> > Thanks!!!
>
>|||Is the cache battery backed up and is the machine on a UPS?
SQL does read the log too.
Paul
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:15119B01-2F8D-464D-A287-75D3CFFDBBD3@.microsoft.com...
> Great, thanks for the reply.
> Dunno. I suppose nothing.
> "David J. Cartwright" wrote:
>> yes, as unless there is a rollback or some recovery operation the log is
>> mostly a write-to file therefore you could reduce some queue time if you
>> put
>> the log on the write cache controller...but what are you going to put ont
>> he
>> read caching controller ?
>> "gracie" <gracie@.discussions.microsoft.com> wrote in message
>> news:A7EEFFFA-A36A-4928-A64C-0A44435CB74A@.microsoft.com...
>> > Currently I have a database server that has the log files on a raid
>> > array
>> > supported by a controller that does NOT support write caching, only
>> > 100%
>> > read. I have another raid controller on the server that does support
>> > write
>> > caching.
>> >
>> > Would there be any performace benefits from having the raid 1 array
>> > with
>> > the
>> > log files supported by the raid controller with write caching over one
>> > that
>> > does not?
>> >
>> > Thanks!!!
>>

RAID 1 and write caching raid controllers

Currently I have a database server that has the log files on a raid array
supported by a controller that does NOT support write caching, only 100%
read. I have another raid controller on the server that does support write
caching.
Would there be any performace benefits from having the raid 1 array with the
log files supported by the raid controller with write caching over one that
does not?
Thanks!!!yes, as unless there is a rollback or some recovery operation the log is
mostly a write-to file therefore you could reduce some queue time if you put
the log on the write cache controller...but what are you going to put ont he
read caching controller ?
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:A7EEFFFA-A36A-4928-A64C-0A44435CB74A@.microsoft.com...
> Currently I have a database server that has the log files on a raid array
> supported by a controller that does NOT support write caching, only 100%
> read. I have another raid controller on the server that does support write
> caching.
> Would there be any performace benefits from having the raid 1 array with
> the
> log files supported by the raid controller with write caching over one
> that
> does not?
> Thanks!!!|||Great, thanks for the reply.
Dunno. I suppose nothing.
"David J. Cartwright" wrote:

> yes, as unless there is a rollback or some recovery operation the log is
> mostly a write-to file therefore you could reduce some queue time if you p
ut
> the log on the write cache controller...but what are you going to put ont
he
> read caching controller ?
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:A7EEFFFA-A36A-4928-A64C-0A44435CB74A@.microsoft.com...
>
>|||Is the cache battery backed up and is the machine on a UPS?
SQL does read the log too.
Paul
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:15119B01-2F8D-464D-A287-75D3CFFDBBD3@.microsoft.com...[vbcol=seagreen]
> Great, thanks for the reply.
> Dunno. I suppose nothing.
> "David J. Cartwright" wrote:
>

R

How do i replace that character in a derived column ?

Some rows have that character in one or more columns. If i just write this

column == "" ? Unknown : column

where the character is inside "" (can't write it here) the task will just succes without ever doing anything. The output says something like "the dataflow task had no tasks.....", which seems like a bug.

Use a conditional statement.

If [column] contains this value, replace it with another, else leave the value alone:

[column] == "A" ? "B" : [column]|||

i know but it's not a common character. Look at the Subject of this question!!!

It's a square character -> <-

It's a non XML valid character

|||

I think thats a new line character.

If you are using an OLEDB source for your data, you can just do a trim() on the column to get rid of it.

|||

If you know the Unicode character value for it, you can use an escape sequence:

"\xhhhh"

where hhhh is the Unicode character value.

Thank
Mark

|||trim() doesn't works. It's doesn't remove the character!|||

hmm and if it's not a unicode character ?

Try putting this in a derived comlumn

REPLACE(TRIM(TXT)," ","")

the task will then complete with this in the output.

Warning: 0x80047034 at Data Flow Task, DTS.Pipeline: The DataFlow task has no components. Add components or remove the task.

|||

Use the REPLACE function, and the Unicode escape sequenece syntax Mark described.

It is a unicode character, as all comparisons are done as Unicode inside the SSIS expression parser, it cannot be anything else as far as SSIS is concerned. You need to find out what that is, and specify it in the REPLACE.

|||

Well but if the character > < is used in a replace within a dataflowtask in ssis, it automatic removes whatever flow you might have build inside that dataflow. In my opinion that seems like a bug..

if you want to do a replace in a sql task, you can't use a direct input (the sql task will then complete as if nothing was typed inside the task). You have to use a file connection for the query, so it seems like this character is the character from hell... :-)

So if you don't have the escape sequenece for this character (can't find it, since you can't search for it :-) ) you'll have to load the entire table to a temp table and do an ordinary replace in a sql query

|||

jam281 wrote:

it automatic removes whatever flow you might have build inside that dataflow.

Can you elaborate on exactly what you mean by "whatever flow you might have build".

-Jamie

|||Download any one of the many free hex editors on the Internet and open up a line of your source in it. Use the hex editor to find the hex value of the character in question. Go from there in your replace function.|||

"Can you elaborate on exactly what you mean by "whatever flow you might have build".

Allright. Try to create a new package - Add a dataflow and open it. Inside the dataflow create an oledb source - point to a table and map it.

Put in a derived column and a do a replace on one of the column like:

CENTRE == " " ? "Unknown" : CENTRE

put in a ole db destination - connect all tree

try to run it and it completes without doing anything. save the package and close the project.

Open the project again and your dataflow is now suddently empty.

The same problem apply if you have copied a sql query inside a sql task and it contains somewhere. The sql task will the execute witout doing anything...

|||

hmm found out that its a char(2) character.

select char(2)

gives that character. Now how do you replace char(2) in a derived column

|||

Let me know if using Unicode escape sequence as follows works for you:

CENTRE == "\x0002" ? "Unknown" : CENTRE

Thanks
Mark

|||

Mark Durley wrote:

Let me know if using Unicode escape sequence as follows works for you:

CENTRE == "\x0002" ? "Unknown" : CENTRE

Thanks
Mark

It worked thanks!!!

R

How do i replace that character in a derived column ?

Some rows have that character in one or more columns. If i just write this

column == "" ? Unknown : column

where the character is inside "" (can't write it here) the task will just succes without ever doing anything. The output says something like "the dataflow task had no tasks.....", which seems like a bug.

Use a conditional statement.

If [column] contains this value, replace it with another, else leave the value alone:

[column] == "A" ? "B" : [column]|||

i know but it's not a common character. Look at the Subject of this question!!!

It's a square character -> <-

It's a non XML valid character

|||

I think thats a new line character.

If you are using an OLEDB source for your data, you can just do a trim() on the column to get rid of it.

|||

If you know the Unicode character value for it, you can use an escape sequence:

"\xhhhh"

where hhhh is the Unicode character value.

Thank
Mark

|||trim() doesn't works. It's doesn't remove the character!|||

hmm and if it's not a unicode character ?

Try putting this in a derived comlumn

REPLACE(TRIM(TXT)," ","")

the task will then complete with this in the output.

Warning: 0x80047034 at Data Flow Task, DTS.Pipeline: The DataFlow task has no components. Add components or remove the task.

|||

Use the REPLACE function, and the Unicode escape sequenece syntax Mark described.

It is a unicode character, as all comparisons are done as Unicode inside the SSIS expression parser, it cannot be anything else as far as SSIS is concerned. You need to find out what that is, and specify it in the REPLACE.

|||

Well but if the character > < is used in a replace within a dataflowtask in ssis, it automatic removes whatever flow you might have build inside that dataflow. In my opinion that seems like a bug..

if you want to do a replace in a sql task, you can't use a direct input (the sql task will then complete as if nothing was typed inside the task). You have to use a file connection for the query, so it seems like this character is the character from hell... :-)

So if you don't have the escape sequenece for this character (can't find it, since you can't search for it :-) ) you'll have to load the entire table to a temp table and do an ordinary replace in a sql query

|||

jam281 wrote:

it automatic removes whatever flow you might have build inside that dataflow.

Can you elaborate on exactly what you mean by "whatever flow you might have build".

-Jamie

|||Download any one of the many free hex editors on the Internet and open up a line of your source in it. Use the hex editor to find the hex value of the character in question. Go from there in your replace function.|||

"Can you elaborate on exactly what you mean by "whatever flow you might have build".

Allright. Try to create a new package - Add a dataflow and open it. Inside the dataflow create an oledb source - point to a table and map it.

Put in a derived column and a do a replace on one of the column like:

CENTRE == " " ? "Unknown" : CENTRE

put in a ole db destination - connect all tree

try to run it and it completes without doing anything. save the package and close the project.

Open the project again and your dataflow is now suddently empty.

The same problem apply if you have copied a sql query inside a sql task and it contains somewhere. The sql task will the execute witout doing anything...

|||

hmm found out that its a char(2) character.

select char(2)

gives that character. Now how do you replace char(2) in a derived column

|||

Let me know if using Unicode escape sequence as follows works for you:

CENTRE == "\x0002" ? "Unknown" : CENTRE

Thanks
Mark

|||

Mark Durley wrote:

Let me know if using Unicode escape sequence as follows works for you:

CENTRE == "\x0002" ? "Unknown" : CENTRE

Thanks
Mark

It worked thanks!!!

qyering data from two databases on different servers

Hello-

I am trying to write a report that combines data from two different databases on two different servers. Wherther I run the query in SQL Server 2005 management studio or reporting services, I get the same error:

Msg 208, Level 16, State 1, Line 1

Invalid object name 'King_County_SWD.dbo.vwDriver_Out_Gate'.

There is an error in the query. Invalid object name 'King_County_SWD.dbo.vwDriver_Out_Gate'.

I have written queries that access two different databases on the same server and they work just fine, so I am guessing that I am not providing enough of a fully qualified path to the second server. We have not implemented linked server.

Any ideas on how to properly identify the second server/database/object would be appreciated.

Whenever I had to access data from 2 different servers, I had to setup a Linked Server. Without going into too much detail, here's an example:

Server1(where the sql statement will be run) Server2(other data)

ON Server1 create a linked server to Server2. On Server1, run your sql statement, BUT for the data on Server2 you need to add the qualifier Server2, i.e.,

Select * from DB1.dbo.table1 inner join Server2.DB2.dbo.table2 on ..............

|||

You could also use an SSIS package as data source for your report.

Sounds weird but works:
http://www.fits-consulting.de/blog/PermaLink,guid,0e3316ae-c9e7-426e-9e6b-30dab0ea2ed2.aspx

cheers,
Markus

sql

quicker way of writing a LIKE query

Hi

I have two product tables in two different databases, both contain thousands of records. I have to write a query that suggests matches on similar codes, and have come up with:

SELECT TB1.product, TB2.product
FROM TB1
JOIN (select distinct product
from db2.dbo.TB2) as TB2 --this table has PK of product and warehouse
ON TB2.product LIKE '%' + TB1.product+'%'

which DOES work, but because the table have many rows,takes time to do it... is there a way of rewritting this query, so it gives a faster result?

Thanks in advance...The problem that I see is that you are using a definition that requires a table scan for TB2 in order to determine row-by-row if the value of TB1.product exists anywhere in TB2.product.

This type of search is an ugly process to implement using just a set based language like SQL. This kind of problem is why Full Text Search (http://msdn2.microsoft.com/en-us/library/ms142571.aspx) was added to MS-SQL. Beware, in that Full Text Search is definitely NOT a "free lunch", there is definitely an overhead cost associated with it.

There are other ways to speed up the process, but none of them are very pretty. My first thought is to evaluate the cost/benefit of using Full Text Search, and only to pursue other answers if you decide not to use it and really need something else.

-PatP|||i would like to see the WHERE clause using full text, please

WHERE CONTAINS( ... ??

your guidance here, pat, will, as usual, be deeply appreciated|||I'd like to see some sample data, with further clarification on what he considers a partial match.|||who said partial match?

here's some sample data showing columns which match
TB1.product TB2.product
shampoo Kerastase Resistance Bain Volumactive Shampoo Volumizing
philosophy cinnamon buns shampoo, conditioner, & shower gel
H2O Plus Sea Marine Revitalizing Shampoo
shaving Proraso Eucalyptus & Menthol Shaving Cream 150 ml.
The Art of Shaving Unscented Pre-Shave Oil
Tweezerman Badger Hair Shaving Brush|||who said partial match?LIKE implies partial matches, whether he wishes or not. And where did you get his data, or did I miss a smiley somewhere?|||And where did you get his datai made it up

his first post said that his query works

this data fits that query

are you smiley-deprived? here, have a few: :) ;) :blush: :rolleyes:|||Thanks. I needed those.|||i would like to see the WHERE clause using full text, pleaseI know... As you are fond of reminding me, you are so NOT a DBA. This one falls outside of the scope of solutions in which you like to play, it is one of those tasks where you just get the job done and move on with life.

The CONTAINS function doesn't work the way you are implying, I don't know of a completely set-based solution for this kind of problem. I would retrieve the rows from the smaller table to a client (such as VBA within a DTS package), and build a temp table of the matches or partial matches so that I could return that. You could also do it with a cursor and dynamic SQL, but that strikes me as even uglier. This is ugly, but it will perform better than the "brute force" of the LIKE approach.

-PatP|||hey

Thanks for all the replies...

I had advanced a wee bit...
Basically I have been able to cut down the amount of rows in TB1 on some factors, and dumped it into a temp table...

I will look into the Full Text Search : )

Thanks again

quick SELECT statement question

Hello all!
I have a date/time column on my table. How can I write a select statement
to pick only items from todays date? I looked, but can't find an answer.
SELECT * User FROM TABLE WHERE datecolumn = "todays date"
Thanks!
RudySELECT
*
FROM
Table
WHERE
CONVERT(CHAR(8), datecolumn, 112) = CONVERT(CHAR(8),
GETDATE(), 112)
Due to the time that is stored in a datatime datatype, you need to strip the
time out as above.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rudy" <Rudy@.discussions.microsoft.com> wrote in message
news:5E70E4E8-EB91-4DBF-8489-5F9E3A5A204C@.microsoft.com...
> Hello all!
> I have a date/time column on my table. How can I write a select statement
> to pick only items from todays date? I looked, but can't find an answer.
> SELECT * User FROM TABLE WHERE datecolumn = "todays date"
> Thanks!
> Rudy|||Hi Mike!
WOW!! I wouild have never figured that out. Thanks!!
Rudy
"Mike Epprecht (SQL MVP)" wrote:

> SELECT
> *
> FROM
> Table
> WHERE
> CONVERT(CHAR(8), datecolumn, 112) = CONVERT(CHAR(8),
> GETDATE(), 112)
> Due to the time that is stored in a datatime datatype, you need to strip t
he
> time out as above.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Rudy" <Rudy@.discussions.microsoft.com> wrote in message
> news:5E70E4E8-EB91-4DBF-8489-5F9E3A5A204C@.microsoft.com...
>
>|||Hi
Or
SELECT
*
FROM
Table
WHERE
datecolumn >= (CONVERT(CHAR(8), GETDATE(), 112) + '
00:00:00.000'
AND datecolumn <= (CONVERT(CHAR(8), GETDATE(), 112) + '
23:59:59.997'
The 1st one can not use an index if that is the only predicate in the where
clause as each row needs to be evaluated.
The 2nd one could use an index, but make sure that you use ' 23:59:59.997'
and not ' 23:59:59.999' as .999 can not be represented in datetime, so it
rounds itself to 00:00:00.000, the next day
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rudy" <Rudy@.discussions.microsoft.com> wrote in message
news:2202B61C-5A3A-4FEB-B0E4-6FC0480FB1EA@.microsoft.com...
> Hi Mike!
> WOW!! I wouild have never figured that out. Thanks!!
> Rudy
> "Mike Epprecht (SQL MVP)" wrote:
>|||In addition to Mike's comments, you might want to red more about the subject
at:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rudy" <Rudy@.discussions.microsoft.com> wrote in message
news:5E70E4E8-EB91-4DBF-8489-5F9E3A5A204C@.microsoft.com...
> Hello all!
> I have a date/time column on my table. How can I write a select statement
> to pick only items from todays date? I looked, but can't find an answer.
> SELECT * User FROM TABLE WHERE datecolumn = "todays date"
> Thanks!
> Rudy

Quick select statement question

hi all

another easy one

how would i write it to where i select a value = 0 where all are equal to 0 rather than one line. when selecting based off a key from another table how would i select the values that all bring back 0 for that key rather than just a line or 2? i hope that makes sense, lol.What have you come up with so far?|||select case when exists(select * from MyTable where MyValue <> 0) then 1 else 0 end|||SELECT dbo.tPA00175.chrJobNumber, dbo.tPA00175.intJobKey, dbo.tPA00125.numQuantityToInv
FROM dbo.tPA00125 INNER JOIN
dbo.tPA00175 ON dbo.tPA00125.intJobKey = dbo.tPA00175.intJobKey
WHERE (dbo.tPA00125.numQuantityToInv = 0)

but this is only selecting the single values...id like to select the ones that have all 0's for the particular jobkey|||How about:

select dbo.tPA00175.chrJobNumber,
dbo.tPA00175.intJobKey,
numQuantityToInv = 0
from dbo.tPA00175 inner join (select intJobKey
from dbo.tPA00125
group by intJobKey
having max(case when numQuantityToInv = 0 then 0 else 1 end) = 0
) as t2 on dbo.tPA00175.intJobKey = t2.intJobKey|||thanks, that works nicely

quick question

Write a sql that divides 8 by 3 cast 3 as a decimal rounded to 2 places \
???????????
HOW do i do this im confused and new to this!!!At least one of your values must be non-integer, and you must cast your final results to two decimal places of accuracy. Otherwise, SQL Server works with integers by default becuase they can be processed more quickly, and it will thus round off your answer.

All these work:
select cast(8/cast(3 as decimal(10,2)) as decimal(10,2))
select cast(cast(8 as decimal(10,2))/cast(3 as decimal(10,2)) as decimal(10,2))
select cast(8/3.0 as decimal(10,2))

...but this does not:
select cast(8/3 as decimal(10,2))

blindman|||Thanks a lot!!!|||bm: SQL does not choose integers because they are easy to work with...That's funny even to think that way :)

Read BOL on datatype conversion rules!!!|||rdj, you need to give it a rest already.|||Pigeon: I would, but bm never gives up :) Plus, his last answer was really funny, so I couldn't resist :D|||You're welcom, dirtysouthchick!

Glad I could be of help to you.

blindman|||go to bed, bm, tomorrow is just another day :)|||Just curious rdjabarov, why the beef with Blindie?|||Nevermind, I just caught up reading the other posts :)|||I don't have a beef, I just see that sometimes all of us (or some of us) prefer a p***ing contest over the opportunity to hear each other out. And I am as guilty in doing this as the next guy. I just don't want to be a part of a place like this, so I hope that we all re-think the reason why we chose to be here in the first place.

Other than that, - life is good :)

Queueing log messages

I have a large web application with several web servers and several sql
servers. For debugging and usage tracking, the web applications write log
messages to a common log database on one of the sql servers. This traffic can
get quite extensive and, although desirable, it should not interfere with the
mainline user processing if possible.
In a previous project, I used an MSMQ to decouple the logging activity from
the mainline processing. The web applications wrote their messages to the
queue, which was a very fast operation, and the queue buffered the entry of
the log data into the database queue. In this case, we wrote a custom service
that read the queue and inserted the results into the database table.
With all the new fancy features of SS 2005 and .NET 2.0, I wonder if there
isn't a better way to do this. For example, would using Service Broker queues
be an option? Although most of the examples I've seen talk about sending
messages from one database to another, it looks like you can write C# code to
send SSB messages from an external application - ie. the web app. Then we'd
write an activation method on the queue that would simply write the log
messages into the database.
How would this work? Or is inserting someting into an SSB queue about the
same as just inserting the log record into the table itself, from a
performance point of view?
Are SSB queues implemented with MSMQ? or something else?
Or should I stick with MSMQ itself? Writing the application end (ie. the
sending end) of the queue is easy; but is there a better way to handle the
receiving end than building a whole service? For example, is there some way
to use CLR integration - or something else - to directly read from the queue
and insert rows into the log table?
All suggestions gratefully accepted
...Mike
> How would this work? Or is inserting someting into an SSB queue about the
> same as just inserting the log record into the table itself, from a
> performance point of view?
I would expect that inserting directly into the log table would generally be
more efficient than a Service Broker queue or a transactional MSMQ for such
a simple task. The issue is that a synchronous physical write is required
to guarantee message delivery regardless of the technology. Service Broker,
MSMQ or a regular table insert must all wait for a write to complete before
returning to back to the client. Service Broker's sweet spot is more for
asynchronous processing and scale-out.
If you don't need guaranteed delivery, I think a non-transactional MSMQ
would probably provide the best response time. However, if your log table
is optimized for writes, I think it would be a close call between the
inserts and MSMQ. I suggest you run performance tests to determine the best
approach for your environment.

> Are SSB queues implemented with MSMQ? or something else?
Queues are schema-owned objects much like regular tables.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike Kraley" <mkraley@.community.nospam> wrote in message
news:24174429-AD79-47DF-AE08-E2C2D62ACFF6@.microsoft.com...
>I have a large web application with several web servers and several sql
> servers. For debugging and usage tracking, the web applications write log
> messages to a common log database on one of the sql servers. This traffic
> can
> get quite extensive and, although desirable, it should not interfere with
> the
> mainline user processing if possible.
> In a previous project, I used an MSMQ to decouple the logging activity
> from
> the mainline processing. The web applications wrote their messages to the
> queue, which was a very fast operation, and the queue buffered the entry
> of
> the log data into the database queue. In this case, we wrote a custom
> service
> that read the queue and inserted the results into the database table.
> With all the new fancy features of SS 2005 and .NET 2.0, I wonder if there
> isn't a better way to do this. For example, would using Service Broker
> queues
> be an option? Although most of the examples I've seen talk about sending
> messages from one database to another, it looks like you can write C# code
> to
> send SSB messages from an external application - ie. the web app. Then
> we'd
> write an activation method on the queue that would simply write the log
> messages into the database.
> How would this work? Or is inserting someting into an SSB queue about the
> same as just inserting the log record into the table itself, from a
> performance point of view?
> Are SSB queues implemented with MSMQ? or something else?
> Or should I stick with MSMQ itself? Writing the application end (ie. the
> sending end) of the queue is easy; but is there a better way to handle the
> receiving end than building a whole service? For example, is there some
> way
> to use CLR integration - or something else - to directly read from the
> queue
> and insert rows into the log table?
> All suggestions gratefully accepted
> --
> ...Mike
|||Thanks to both Dan and Charles for your replies. A few followups:
The issue I'm really worried about here is the backup of inserting many
items into a potentially large table, ie. the log table. I don't want this
overhead to be in series with the "real" transactions.
1. What do you think about using a direct insert into the log table, but
using an async "fire and forget" call?
2. If I do go with the MSMQ solution, is there a better way than an external
service? Is there some way to use SQL Server's CLR to read from the queue? I
suspect this is possible, but I haven't found a good example of this
anywhere. Any suggestions?
3. Dan, you mentioned "if your log table is optimized for writes" - what did
you have in mind here?
thanks...Mike
|||Hi Mike,
For your questions:
> 1. What do you think about using a direct insert into the log table, but
> using an async "fire and forget" call?
If there is no explicit performance issues, it is no problem to use a
direct insert into a log table. Actually this is the most common way in
normal systems. For your system, I think that async "fire and forget" may
work more efficient since your system has intensive logging activities.

> 2. If I do go with the MSMQ solution, is there a better way than an
external
> service? Is there some way to use SQL Server's CLR to read from the
queue? I
> suspect this is possible, but I haven't found a good example of this
> anywhere. Any suggestions?
As far as I know, it is not possible to use SQL Server CLR to read from the
queue since SQL Server CLR cannot reference non-SQL Server projects. Could
you please let me know why you want to use SQL Server CLR to read from the
queue?
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Hi Mike,
For your questions:
> 1. What do you think about using a direct insert into the log table, but
> using an async "fire and forget" call?
If there is no explicit performance issues, it is no problem to use a
direct insert into a log table. Actually this is the most common way in
normal systems. For your system, I think that async "fire and forget" may
work more efficient since your system has intensive logging activities.

> 2. If I do go with the MSMQ solution, is there a better way than an
external
> service? Is there some way to use SQL Server's CLR to read from the
queue? I
> suspect this is possible, but I haven't found a good example of this
> anywhere. Any suggestions?
As far as I know, it is not possible to use SQL Server CLR to read from the
queue since SQL Server CLR cannot reference non-SQL Server projects. Could
you please let me know why you want to use SQL Server CLR to read from the
queue?
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||> 1. What do you think about using a direct insert into the log table, but
> using an async "fire and forget" call?
I think an asynch call is a good idea. I don't know about the "forget" part
though; I think you'd want to know if it succeeded ;-)

> 2. If I do go with the MSMQ solution, is there a better way than an
> external
> service? Is there some way to use SQL Server's CLR to read from the queue?
> I
> suspect this is possible, but I haven't found a good example of this
> anywhere. Any suggestions?
A quick Google search turned up a MSMQ example at
(http://www.codeproject.com/useritems/SqlMSMQ.asp) but I haven't looked at
it. Although it may be possible for the SQL CLR to host the app, I don't
see much value in doing so. The service method you mentioned is the most
elegant but you could easily invoke a command-line utility using a
continuously running SQL Agent job that launches at startup.

> 3. Dan, you mentioned "if your log table is optimized for writes" - what
> did
> you have in mind here?
Specifically, I meant a table that has only a clustered index with a
increasing key (e.g. IDENTITY column or log datetime). This will perform
very well regardless of table size. I've successfully used that approach
with tables containing billions of rows.
You might be able to get away with additional non-clustered indexes too
depending on the table size and your i/o subsystem. Once you reach a
certain threshold, consider partitioning the table by date. This will keep
the current day data working set small and mitigate the overhead of
maintaining those non-clustered indexes while improve manageability.
Without table partitioning, you still have the option of moving data via a
daily process into an appropriately indexed reporting table. This works
best when current data are infrequently accessed and most queries are done
against historical data. You can use a view (perhaps partitioned) to make
the implementation abstract.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike Kraley" <mkraley@.community.nospam> wrote in message
news:48AC1D2D-73DE-4934-AE4A-57EFAB36333F@.microsoft.com...
> Thanks to both Dan and Charles for your replies. A few followups:
> The issue I'm really worried about here is the backup of inserting many
> items into a potentially large table, ie. the log table. I don't want this
> overhead to be in series with the "real" transactions.
> 1. What do you think about using a direct insert into the log table, but
> using an async "fire and forget" call?
> 2. If I do go with the MSMQ solution, is there a better way than an
> external
> service? Is there some way to use SQL Server's CLR to read from the queue?
> I
> suspect this is possible, but I haven't found a good example of this
> anywhere. Any suggestions?
> 3. Dan, you mentioned "if your log table is optimized for writes" - what
> did
> you have in mind here?
> thanks...Mike
|||Thanks again, both of you.
- why not use a service? Just because it is yet another "thing" that has to
be built, tested, deployed, maintained, operated, etc. If it can all be
magically included in the database, that seems a bit easier. Of course the
service is a workable solution if the DB can't do the job.
The SQL Agent task is another good idea.
The tips on table organization are also very helpful
...Mike
|||MSMQ will give you decoupling and not much more.
Service Broker will give you integrated storage of messages and data (i.e.
you have only one product/database to backup/restore), integration with
database clustering and database mirroring. It also gives you activation of
T-SQL or CLR procedures to process the messages. It provides guaranteed EOIO
(Exactly Once In Order) semantics for your messages and reliable
communication shutdown and error (think TCP (broker) vs. UDP (msmq)),
decouples physical location from logical destination (databases containing
Service Broker queues can be moved to new hosts and continue the existing
messaging sessions)
For what you describe there are two usual patterns:
- one SQL Server all applications connect to and send a message (using T-SQL
SEND verb), which is usefull when the goal is to return quickly control to
the application and let the processing happen asynchronously. This gives
decoupling from processing, but not from availability, i.e. if the SQL
server is down the application cannot log
- Each application (Web server) has a local SQL Express instance to which it
connects and issues the SEND verb and lest the Express isntance handle the
delivery of the message to the central log. This gives decoupling both from
processing and availability. The SQL Express availability is usually same as
the Web server availability. The trouble of deploying a SQL Express instance
on each web host is about the same as deploying a msmq queue, since this is
not a full blown SQL Server instance that needs maintenance and
administration, once deployed it can pretty much go on auto-pilot mode.
Have a look at the slides at
http://blogs.msdn.com/remusrusanu/archive/2007/04/03/orlando-slides-and-code.aspx
HTH,
~ Remus
"Mike Kraley" <mkraley@.community.nospam> wrote in message
news:24174429-AD79-47DF-AE08-E2C2D62ACFF6@.microsoft.com...
>I have a large web application with several web servers and several sql
> servers. For debugging and usage tracking, the web applications write log
> messages to a common log database on one of the sql servers. This traffic
> can
> get quite extensive and, although desirable, it should not interfere with
> the
> mainline user processing if possible.
> In a previous project, I used an MSMQ to decouple the logging activity
> from
> the mainline processing. The web applications wrote their messages to the
> queue, which was a very fast operation, and the queue buffered the entry
> of
> the log data into the database queue. In this case, we wrote a custom
> service
> that read the queue and inserted the results into the database table.
> With all the new fancy features of SS 2005 and .NET 2.0, I wonder if there
> isn't a better way to do this. For example, would using Service Broker
> queues
> be an option? Although most of the examples I've seen talk about sending
> messages from one database to another, it looks like you can write C# code
> to
> send SSB messages from an external application - ie. the web app. Then
> we'd
> write an activation method on the queue that would simply write the log
> messages into the database.
> How would this work? Or is inserting someting into an SSB queue about the
> same as just inserting the log record into the table itself, from a
> performance point of view?
> Are SSB queues implemented with MSMQ? or something else?
> Or should I stick with MSMQ itself? Writing the application end (ie. the
> sending end) of the queue is easy; but is there a better way to handle the
> receiving end than building a whole service? For example, is there some
> way
> to use CLR integration - or something else - to directly read from the
> queue
> and insert rows into the log table?
> All suggestions gratefully accepted
> --
> ...Mike

Queueing log messages

I have a large web application with several web servers and several sql
servers. For debugging and usage tracking, the web applications write log
messages to a common log database on one of the sql servers. This traffic ca
n
get quite extensive and, although desirable, it should not interfere with th
e
mainline user processing if possible.
In a previous project, I used an MSMQ to decouple the logging activity from
the mainline processing. The web applications wrote their messages to the
queue, which was a very fast operation, and the queue buffered the entry of
the log data into the database queue. In this case, we wrote a custom servic
e
that read the queue and inserted the results into the database table.
With all the new fancy features of SS 2005 and .NET 2.0, I wonder if there
isn't a better way to do this. For example, would using Service Broker queue
s
be an option? Although most of the examples I've seen talk about sending
messages from one database to another, it looks like you can write C# code t
o
send SSB messages from an external application - ie. the web app. Then we'd
write an activation method on the queue that would simply write the log
messages into the database.
How would this work? Or is inserting someting into an SSB queue about the
same as just inserting the log record into the table itself, from a
performance point of view?
Are SSB queues implemented with MSMQ? or something else?
Or should I stick with MSMQ itself? Writing the application end (ie. the
sending end) of the queue is easy; but is there a better way to handle the
receiving end than building a whole service? For example, is there some way
to use CLR integration - or something else - to directly read from the queue
and insert rows into the log table?
All suggestions gratefully accepted
...Mike> How would this work? Or is inserting someting into an SSB queue about the
> same as just inserting the log record into the table itself, from a
> performance point of view?
I would expect that inserting directly into the log table would generally be
more efficient than a Service Broker queue or a transactional MSMQ for such
a simple task. The issue is that a synchronous physical write is required
to guarantee message delivery regardless of the technology. Service Broker,
MSMQ or a regular table insert must all wait for a write to complete before
returning to back to the client. Service Broker's sweet spot is more for
asynchronous processing and scale-out.
If you don't need guaranteed delivery, I think a non-transactional MSMQ
would probably provide the best response time. However, if your log table
is optimized for writes, I think it would be a close call between the
inserts and MSMQ. I suggest you run performance tests to determine the best
approach for your environment.

> Are SSB queues implemented with MSMQ? or something else?
Queues are schema-owned objects much like regular tables.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike Kraley" <mkraley@.community.nospam> wrote in message
news:24174429-AD79-47DF-AE08-E2C2D62ACFF6@.microsoft.com...
>I have a large web application with several web servers and several sql
> servers. For debugging and usage tracking, the web applications write log
> messages to a common log database on one of the sql servers. This traffic
> can
> get quite extensive and, although desirable, it should not interfere with
> the
> mainline user processing if possible.
> In a previous project, I used an MSMQ to decouple the logging activity
> from
> the mainline processing. The web applications wrote their messages to the
> queue, which was a very fast operation, and the queue buffered the entry
> of
> the log data into the database queue. In this case, we wrote a custom
> service
> that read the queue and inserted the results into the database table.
> With all the new fancy features of SS 2005 and .NET 2.0, I wonder if there
> isn't a better way to do this. For example, would using Service Broker
> queues
> be an option? Although most of the examples I've seen talk about sending
> messages from one database to another, it looks like you can write C# code
> to
> send SSB messages from an external application - ie. the web app. Then
> we'd
> write an activation method on the queue that would simply write the log
> messages into the database.
> How would this work? Or is inserting someting into an SSB queue about the
> same as just inserting the log record into the table itself, from a
> performance point of view?
> Are SSB queues implemented with MSMQ? or something else?
> Or should I stick with MSMQ itself? Writing the application end (ie. the
> sending end) of the queue is easy; but is there a better way to handle the
> receiving end than building a whole service? For example, is there some
> way
> to use CLR integration - or something else - to directly read from the
> queue
> and insert rows into the log table?
> All suggestions gratefully accepted
> --
> ...Mike|||Hi Mike,
Did you encounter any issue of using MSMQ? If MSMQ worked fine, I think
that it is no need to change your current implementation. SQL Server
Service Broker also provides a queue for messages; however it is not MSMQ.
The explicit difference between Service Broker and MSMQ is that for Service
Broker you can use T-SQL to send messages while for MSMQ you need to run
MSMQ API to send messages.
For more detailed information, y ou may refer to:
Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043.aspx
From your description, I saw that your MSMQ resolution seemed efficient and
worked fine now, so my recommendation here is just keeping it there until
it does not satisfy your requirements.
If you have any other questions or concerns, please feel free to let me
know. Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Thanks to both Dan and Charles for your replies. A few followups:
The issue I'm really worried about here is the backup of inserting many
items into a potentially large table, ie. the log table. I don't want this
overhead to be in series with the "real" transactions.
1. What do you think about using a direct insert into the log table, but
using an async "fire and forget" call?
2. If I do go with the MSMQ solution, is there a better way than an external
service? Is there some way to use SQL Server's CLR to read from the queue? I
suspect this is possible, but I haven't found a good example of this
anywhere. Any suggestions?
3. Dan, you mentioned "if your log table is optimized for writes" - what did
you have in mind here?
thanks...Mike|||Hi Mike,
For your questions:
> 1. What do you think about using a direct insert into the log table, but
> using an async "fire and forget" call?
If there is no explicit performance issues, it is no problem to use a
direct insert into a log table. Actually this is the most common way in
normal systems. For your system, I think that async "fire and forget" may
work more efficient since your system has intensive logging activities.

> 2. If I do go with the MSMQ solution, is there a better way than an
external
> service? Is there some way to use SQL Server's CLR to read from the
queue? I
> suspect this is possible, but I haven't found a good example of this
> anywhere. Any suggestions?
As far as I know, it is not possible to use SQL Server CLR to read from the
queue since SQL Server CLR cannot reference non-SQL Server projects. Could
you please let me know why you want to use SQL Server CLR to read from the
queue?
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Hi Mike,
For your questions:
> 1. What do you think about using a direct insert into the log table, but
> using an async "fire and forget" call?
If there is no explicit performance issues, it is no problem to use a
direct insert into a log table. Actually this is the most common way in
normal systems. For your system, I think that async "fire and forget" may
work more efficient since your system has intensive logging activities.

> 2. If I do go with the MSMQ solution, is there a better way than an
external
> service? Is there some way to use SQL Server's CLR to read from the
queue? I
> suspect this is possible, but I haven't found a good example of this
> anywhere. Any suggestions?
As far as I know, it is not possible to use SQL Server CLR to read from the
queue since SQL Server CLR cannot reference non-SQL Server projects. Could
you please let me know why you want to use SQL Server CLR to read from the
queue?
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||> 1. What do you think about using a direct insert into the log table, but
> using an async "fire and forget" call?
I think an asynch call is a good idea. I don't know about the "forget" part
though; I think you'd want to know if it succeeded ;-)

> 2. If I do go with the MSMQ solution, is there a better way than an
> external
> service? Is there some way to use SQL Server's CLR to read from the queue?
> I
> suspect this is possible, but I haven't found a good example of this
> anywhere. Any suggestions?
A quick Google search turned up a MSMQ example at
(http://www.codeproject.com/useritems/SqlMSMQ.asp) but I haven't looked at
it. Although it may be possible for the SQL CLR to host the app, I don't
see much value in doing so. The service method you mentioned is the most
elegant but you could easily invoke a command-line utility using a
continuously running SQL Agent job that launches at startup.

> 3. Dan, you mentioned "if your log table is optimized for writes" - what
> did
> you have in mind here?
Specifically, I meant a table that has only a clustered index with a
increasing key (e.g. IDENTITY column or log datetime). This will perform
very well regardless of table size. I've successfully used that approach
with tables containing billions of rows.
You might be able to get away with additional non-clustered indexes too
depending on the table size and your i/o subsystem. Once you reach a
certain threshold, consider partitioning the table by date. This will keep
the current day data working set small and mitigate the overhead of
maintaining those non-clustered indexes while improve manageability.
Without table partitioning, you still have the option of moving data via a
daily process into an appropriately indexed reporting table. This works
best when current data are infrequently accessed and most queries are done
against historical data. You can use a view (perhaps partitioned) to make
the implementation abstract.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike Kraley" <mkraley@.community.nospam> wrote in message
news:48AC1D2D-73DE-4934-AE4A-57EFAB36333F@.microsoft.com...
> Thanks to both Dan and Charles for your replies. A few followups:
> The issue I'm really worried about here is the backup of inserting many
> items into a potentially large table, ie. the log table. I don't want this
> overhead to be in series with the "real" transactions.
> 1. What do you think about using a direct insert into the log table, but
> using an async "fire and forget" call?
> 2. If I do go with the MSMQ solution, is there a better way than an
> external
> service? Is there some way to use SQL Server's CLR to read from the queue?
> I
> suspect this is possible, but I haven't found a good example of this
> anywhere. Any suggestions?
> 3. Dan, you mentioned "if your log table is optimized for writes" - what
> did
> you have in mind here?
> thanks...Mike|||Thanks again, both of you.
- why not use a service? Just because it is yet another "thing" that has to
be built, tested, deployed, maintained, operated, etc. If it can all be
magically included in the database, that seems a bit easier. Of course the
service is a workable solution if the DB can't do the job.
The SQL Agent task is another good idea.
The tips on table organization are also very helpful
...Mike|||MSMQ will give you decoupling and not much more.
Service Broker will give you integrated storage of messages and data (i.e.
you have only one product/database to backup/restore), integration with
database clustering and database mirroring. It also gives you activation of
T-SQL or CLR procedures to process the messages. It provides guaranteed EOIO
(Exactly Once In Order) semantics for your messages and reliable
communication shutdown and error (think TCP (broker) vs. UDP (msmq)),
decouples physical location from logical destination (databases containing
Service Broker queues can be moved to new hosts and continue the existing
messaging sessions)
For what you describe there are two usual patterns:
- one SQL Server all applications connect to and send a message (using T-SQL
SEND verb), which is usefull when the goal is to return quickly control to
the application and let the processing happen asynchronously. This gives
decoupling from processing, but not from availability, i.e. if the SQL
server is down the application cannot log
- Each application (Web server) has a local SQL Express instance to which it
connects and issues the SEND verb and lest the Express isntance handle the
delivery of the message to the central log. This gives decoupling both from
processing and availability. The SQL Express availability is usually same as
the Web server availability. The trouble of deploying a SQL Express instance
on each web host is about the same as deploying a msmq queue, since this is
not a full blown SQL Server instance that needs maintenance and
administration, once deployed it can pretty much go on auto-pilot mode.
Have a look at the slides at
[url]http://blogs.msdn.com/remusrusanu/archive/2007/04/03/orlando-slides-and-code.aspx[
/url]
HTH,
~ Remus
"Mike Kraley" <mkraley@.community.nospam> wrote in message
news:24174429-AD79-47DF-AE08-E2C2D62ACFF6@.microsoft.com...
>I have a large web application with several web servers and several sql
> servers. For debugging and usage tracking, the web applications write log
> messages to a common log database on one of the sql servers. This traffic
> can
> get quite extensive and, although desirable, it should not interfere with
> the
> mainline user processing if possible.
> In a previous project, I used an MSMQ to decouple the logging activity
> from
> the mainline processing. The web applications wrote their messages to the
> queue, which was a very fast operation, and the queue buffered the entry
> of
> the log data into the database queue. In this case, we wrote a custom
> service
> that read the queue and inserted the results into the database table.
> With all the new fancy features of SS 2005 and .NET 2.0, I wonder if there
> isn't a better way to do this. For example, would using Service Broker
> queues
> be an option? Although most of the examples I've seen talk about sending
> messages from one database to another, it looks like you can write C# code
> to
> send SSB messages from an external application - ie. the web app. Then
> we'd
> write an activation method on the queue that would simply write the log
> messages into the database.
> How would this work? Or is inserting someting into an SSB queue about the
> same as just inserting the log record into the table itself, from a
> performance point of view?
> Are SSB queues implemented with MSMQ? or something else?
> Or should I stick with MSMQ itself? Writing the application end (ie. the
> sending end) of the queue is easy; but is there a better way to handle the
> receiving end than building a whole service? For example, is there some
> way
> to use CLR integration - or something else - to directly read from the
> queue
> and insert rows into the log table?
> All suggestions gratefully accepted
> --
> ...Mike

Queueing log messages

I have a large web application with several web servers and several sql
servers. For debugging and usage tracking, the web applications write log
messages to a common log database on one of the sql servers. This traffic can
get quite extensive and, although desirable, it should not interfere with the
mainline user processing if possible.
In a previous project, I used an MSMQ to decouple the logging activity from
the mainline processing. The web applications wrote their messages to the
queue, which was a very fast operation, and the queue buffered the entry of
the log data into the database queue. In this case, we wrote a custom service
that read the queue and inserted the results into the database table.
With all the new fancy features of SS 2005 and .NET 2.0, I wonder if there
isn't a better way to do this. For example, would using Service Broker queues
be an option? Although most of the examples I've seen talk about sending
messages from one database to another, it looks like you can write C# code to
send SSB messages from an external application - ie. the web app. Then we'd
write an activation method on the queue that would simply write the log
messages into the database.
How would this work? Or is inserting someting into an SSB queue about the
same as just inserting the log record into the table itself, from a
performance point of view?
Are SSB queues implemented with MSMQ? or something else?
Or should I stick with MSMQ itself? Writing the application end (ie. the
sending end) of the queue is easy; but is there a better way to handle the
receiving end than building a whole service? For example, is there some way
to use CLR integration - or something else - to directly read from the queue
and insert rows into the log table?
All suggestions gratefully accepted
--
...Mike> How would this work? Or is inserting someting into an SSB queue about the
> same as just inserting the log record into the table itself, from a
> performance point of view?
I would expect that inserting directly into the log table would generally be
more efficient than a Service Broker queue or a transactional MSMQ for such
a simple task. The issue is that a synchronous physical write is required
to guarantee message delivery regardless of the technology. Service Broker,
MSMQ or a regular table insert must all wait for a write to complete before
returning to back to the client. Service Broker's sweet spot is more for
asynchronous processing and scale-out.
If you don't need guaranteed delivery, I think a non-transactional MSMQ
would probably provide the best response time. However, if your log table
is optimized for writes, I think it would be a close call between the
inserts and MSMQ. I suggest you run performance tests to determine the best
approach for your environment.
> Are SSB queues implemented with MSMQ? or something else?
Queues are schema-owned objects much like regular tables.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike Kraley" <mkraley@.community.nospam> wrote in message
news:24174429-AD79-47DF-AE08-E2C2D62ACFF6@.microsoft.com...
>I have a large web application with several web servers and several sql
> servers. For debugging and usage tracking, the web applications write log
> messages to a common log database on one of the sql servers. This traffic
> can
> get quite extensive and, although desirable, it should not interfere with
> the
> mainline user processing if possible.
> In a previous project, I used an MSMQ to decouple the logging activity
> from
> the mainline processing. The web applications wrote their messages to the
> queue, which was a very fast operation, and the queue buffered the entry
> of
> the log data into the database queue. In this case, we wrote a custom
> service
> that read the queue and inserted the results into the database table.
> With all the new fancy features of SS 2005 and .NET 2.0, I wonder if there
> isn't a better way to do this. For example, would using Service Broker
> queues
> be an option? Although most of the examples I've seen talk about sending
> messages from one database to another, it looks like you can write C# code
> to
> send SSB messages from an external application - ie. the web app. Then
> we'd
> write an activation method on the queue that would simply write the log
> messages into the database.
> How would this work? Or is inserting someting into an SSB queue about the
> same as just inserting the log record into the table itself, from a
> performance point of view?
> Are SSB queues implemented with MSMQ? or something else?
> Or should I stick with MSMQ itself? Writing the application end (ie. the
> sending end) of the queue is easy; but is there a better way to handle the
> receiving end than building a whole service? For example, is there some
> way
> to use CLR integration - or something else - to directly read from the
> queue
> and insert rows into the log table?
> All suggestions gratefully accepted
> --
> ...Mike|||Hi Mike,
Did you encounter any issue of using MSMQ? If MSMQ worked fine, I think
that it is no need to change your current implementation. SQL Server
Service Broker also provides a queue for messages; however it is not MSMQ.
The explicit difference between Service Broker and MSMQ is that for Service
Broker you can use T-SQL to send messages while for MSMQ you need to run
MSMQ API to send messages.
For more detailed information, y ou may refer to:
Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043.aspx
From your description, I saw that your MSMQ resolution seemed efficient and
worked fine now, so my recommendation here is just keeping it there until
it does not satisfy your requirements.
If you have any other questions or concerns, please feel free to let me
know. Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Thanks to both Dan and Charles for your replies. A few followups:
The issue I'm really worried about here is the backup of inserting many
items into a potentially large table, ie. the log table. I don't want this
overhead to be in series with the "real" transactions.
1. What do you think about using a direct insert into the log table, but
using an async "fire and forget" call?
2. If I do go with the MSMQ solution, is there a better way than an external
service? Is there some way to use SQL Server's CLR to read from the queue? I
suspect this is possible, but I haven't found a good example of this
anywhere. Any suggestions?
3. Dan, you mentioned "if your log table is optimized for writes" - what did
you have in mind here?
thanks...Mike|||Hi Mike,
For your questions:
> 1. What do you think about using a direct insert into the log table, but
> using an async "fire and forget" call?
If there is no explicit performance issues, it is no problem to use a
direct insert into a log table. Actually this is the most common way in
normal systems. For your system, I think that async "fire and forget" may
work more efficient since your system has intensive logging activities.
> 2. If I do go with the MSMQ solution, is there a better way than an
external
> service? Is there some way to use SQL Server's CLR to read from the
queue? I
> suspect this is possible, but I haven't found a good example of this
> anywhere. Any suggestions?
As far as I know, it is not possible to use SQL Server CLR to read from the
queue since SQL Server CLR cannot reference non-SQL Server projects. Could
you please let me know why you want to use SQL Server CLR to read from the
queue?
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi Mike,
For your questions:
> 1. What do you think about using a direct insert into the log table, but
> using an async "fire and forget" call?
If there is no explicit performance issues, it is no problem to use a
direct insert into a log table. Actually this is the most common way in
normal systems. For your system, I think that async "fire and forget" may
work more efficient since your system has intensive logging activities.
> 2. If I do go with the MSMQ solution, is there a better way than an
external
> service? Is there some way to use SQL Server's CLR to read from the
queue? I
> suspect this is possible, but I haven't found a good example of this
> anywhere. Any suggestions?
As far as I know, it is not possible to use SQL Server CLR to read from the
queue since SQL Server CLR cannot reference non-SQL Server projects. Could
you please let me know why you want to use SQL Server CLR to read from the
queue?
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||> 1. What do you think about using a direct insert into the log table, but
> using an async "fire and forget" call?
I think an asynch call is a good idea. I don't know about the "forget" part
though; I think you'd want to know if it succeeded ;-)
> 2. If I do go with the MSMQ solution, is there a better way than an
> external
> service? Is there some way to use SQL Server's CLR to read from the queue?
> I
> suspect this is possible, but I haven't found a good example of this
> anywhere. Any suggestions?
A quick Google search turned up a MSMQ example at
(http://www.codeproject.com/useritems/SqlMSMQ.asp) but I haven't looked at
it. Although it may be possible for the SQL CLR to host the app, I don't
see much value in doing so. The service method you mentioned is the most
elegant but you could easily invoke a command-line utility using a
continuously running SQL Agent job that launches at startup.
> 3. Dan, you mentioned "if your log table is optimized for writes" - what
> did
> you have in mind here?
Specifically, I meant a table that has only a clustered index with a
increasing key (e.g. IDENTITY column or log datetime). This will perform
very well regardless of table size. I've successfully used that approach
with tables containing billions of rows.
You might be able to get away with additional non-clustered indexes too
depending on the table size and your i/o subsystem. Once you reach a
certain threshold, consider partitioning the table by date. This will keep
the current day data working set small and mitigate the overhead of
maintaining those non-clustered indexes while improve manageability.
Without table partitioning, you still have the option of moving data via a
daily process into an appropriately indexed reporting table. This works
best when current data are infrequently accessed and most queries are done
against historical data. You can use a view (perhaps partitioned) to make
the implementation abstract.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike Kraley" <mkraley@.community.nospam> wrote in message
news:48AC1D2D-73DE-4934-AE4A-57EFAB36333F@.microsoft.com...
> Thanks to both Dan and Charles for your replies. A few followups:
> The issue I'm really worried about here is the backup of inserting many
> items into a potentially large table, ie. the log table. I don't want this
> overhead to be in series with the "real" transactions.
> 1. What do you think about using a direct insert into the log table, but
> using an async "fire and forget" call?
> 2. If I do go with the MSMQ solution, is there a better way than an
> external
> service? Is there some way to use SQL Server's CLR to read from the queue?
> I
> suspect this is possible, but I haven't found a good example of this
> anywhere. Any suggestions?
> 3. Dan, you mentioned "if your log table is optimized for writes" - what
> did
> you have in mind here?
> thanks...Mike|||Thanks again, both of you.
- why not use a service? Just because it is yet another "thing" that has to
be built, tested, deployed, maintained, operated, etc. If it can all be
magically included in the database, that seems a bit easier. Of course the
service is a workable solution if the DB can't do the job.
The SQL Agent task is another good idea.
The tips on table organization are also very helpful
...Mike|||MSMQ will give you decoupling and not much more.
Service Broker will give you integrated storage of messages and data (i.e.
you have only one product/database to backup/restore), integration with
database clustering and database mirroring. It also gives you activation of
T-SQL or CLR procedures to process the messages. It provides guaranteed EOIO
(Exactly Once In Order) semantics for your messages and reliable
communication shutdown and error (think TCP (broker) vs. UDP (msmq)),
decouples physical location from logical destination (databases containing
Service Broker queues can be moved to new hosts and continue the existing
messaging sessions)
For what you describe there are two usual patterns:
- one SQL Server all applications connect to and send a message (using T-SQL
SEND verb), which is usefull when the goal is to return quickly control to
the application and let the processing happen asynchronously. This gives
decoupling from processing, but not from availability, i.e. if the SQL
server is down the application cannot log
- Each application (Web server) has a local SQL Express instance to which it
connects and issues the SEND verb and lest the Express isntance handle the
delivery of the message to the central log. This gives decoupling both from
processing and availability. The SQL Express availability is usually same as
the Web server availability. The trouble of deploying a SQL Express instance
on each web host is about the same as deploying a msmq queue, since this is
not a full blown SQL Server instance that needs maintenance and
administration, once deployed it can pretty much go on auto-pilot mode.
Have a look at the slides at
http://blogs.msdn.com/remusrusanu/archive/2007/04/03/orlando-slides-and-code.aspx
HTH,
~ Remus
"Mike Kraley" <mkraley@.community.nospam> wrote in message
news:24174429-AD79-47DF-AE08-E2C2D62ACFF6@.microsoft.com...
>I have a large web application with several web servers and several sql
> servers. For debugging and usage tracking, the web applications write log
> messages to a common log database on one of the sql servers. This traffic
> can
> get quite extensive and, although desirable, it should not interfere with
> the
> mainline user processing if possible.
> In a previous project, I used an MSMQ to decouple the logging activity
> from
> the mainline processing. The web applications wrote their messages to the
> queue, which was a very fast operation, and the queue buffered the entry
> of
> the log data into the database queue. In this case, we wrote a custom
> service
> that read the queue and inserted the results into the database table.
> With all the new fancy features of SS 2005 and .NET 2.0, I wonder if there
> isn't a better way to do this. For example, would using Service Broker
> queues
> be an option? Although most of the examples I've seen talk about sending
> messages from one database to another, it looks like you can write C# code
> to
> send SSB messages from an external application - ie. the web app. Then
> we'd
> write an activation method on the queue that would simply write the log
> messages into the database.
> How would this work? Or is inserting someting into an SSB queue about the
> same as just inserting the log record into the table itself, from a
> performance point of view?
> Are SSB queues implemented with MSMQ? or something else?
> Or should I stick with MSMQ itself? Writing the application end (ie. the
> sending end) of the queue is easy; but is there a better way to handle the
> receiving end than building a whole service? For example, is there some
> way
> to use CLR integration - or something else - to directly read from the
> queue
> and insert rows into the log table?
> All suggestions gratefully accepted
> --
> ...Mike

Queue IO

HI !
Does anybody know what is an ideal or problem issue conserning ! Queue I/O (
FOR READ AND WRITE ) ! What number is considered HIGH or normal ! Does anyb
ody know that '
Thank'sHi
There are chapters on this sort of thing in the following book:
w.as
p" target="_blank">http://www.sql-server-performance.c...>
w.as
p
I don't have it with me to look up, but low single figures are ideal! Any
figure that impacts on your SLA or the usability of the system is too
high!!!
John
"Carrasco" <anonymous@.discussions.microsoft.com> wrote in message
news:498F6762-0B5B-4636-97CC-E04B243A54CE@.microsoft.com...
> HI !
> Does anybody know what is an ideal or problem issue conserning ! Queue I/O
( FOR READ AND WRITE ) ! What number is considered HIGH or normal ! Does
anybody know that '
> Thank's
>|||average disk queue over 2.0 is getting high (from my experience)
Greg Jackson
PDX, Oregon|||a general rule of thumb in anything > 2 per spindle is worthy of further
investigation.
ie in a RAID 5 system with 7 disks - > 14 would be considered worthy of
further investigation (7 Disks x 2 per disk)
regards,
Andy.
"Carrasco" <anonymous@.discussions.microsoft.com> wrote in message
news:498F6762-0B5B-4636-97CC-E04B243A54CE@.microsoft.com...
> HI !
> Does anybody know what is an ideal or problem issue conserning ! Queue I/O
( FOR READ AND WRITE ) ! What number is considered HIGH or normal ! Does
anybody know that '
> Thank's
>

Queue IO

HI !
Does anybody know what is an ideal or problem issue conserning ! Queue I/O ( FOR READ AND WRITE ) ! What number is considered HIGH or normal ! Does anybody know that ?
Thank'Hi
There are chapters on this sort of thing in the following book:
http://www.sql-server-performance.com/sql_server_2000_performance_tuning_review.asp
I don't have it with me to look up, but low single figures are ideal! Any
figure that impacts on your SLA or the usability of the system is too
high!!!
John
"Carrasco" <anonymous@.discussions.microsoft.com> wrote in message
news:498F6762-0B5B-4636-97CC-E04B243A54CE@.microsoft.com...
> HI !
> Does anybody know what is an ideal or problem issue conserning ! Queue I/O
( FOR READ AND WRITE ) ! What number is considered HIGH or normal ! Does
anybody know that '
> Thank's
>|||average disk queue over 2.0 is getting high (from my experience)
Greg Jackson
PDX, Oregon|||a general rule of thumb in anything > 2 per spindle is worthy of further
investigation.
ie in a RAID 5 system with 7 disks - > 14 would be considered worthy of
further investigation (7 Disks x 2 per disk)
regards,
Andy.
"Carrasco" <anonymous@.discussions.microsoft.com> wrote in message
news:498F6762-0B5B-4636-97CC-E04B243A54CE@.microsoft.com...
> HI !
> Does anybody know what is an ideal or problem issue conserning ! Queue I/O
( FOR READ AND WRITE ) ! What number is considered HIGH or normal ! Does
anybody know that '
> Thank's
>

Queue IO

HI !
Does anybody know what is an ideal or problem issue conserning ! Queue I/O ( FOR READ AND WRITE ) ! What number is considered HIGH or normal ! Does anybody know that ?
Thank's
Hi
There are chapters on this sort of thing in the following book:
http://www.sql-server-performance.co...ing_review.asp
I don't have it with me to look up, but low single figures are ideal! Any
figure that impacts on your SLA or the usability of the system is too
high!!!
John
"Carrasco" <anonymous@.discussions.microsoft.com> wrote in message
news:498F6762-0B5B-4636-97CC-E04B243A54CE@.microsoft.com...
> HI !
> Does anybody know what is an ideal or problem issue conserning ! Queue I/O
( FOR READ AND WRITE ) ! What number is considered HIGH or normal ! Does
anybody know that ?
> Thank's
>
|||average disk queue over 2.0 is getting high (from my experience)
Greg Jackson
PDX, Oregon
|||a general rule of thumb in anything > 2 per spindle is worthy of further
investigation.
ie in a RAID 5 system with 7 disks - > 14 would be considered worthy of
further investigation (7 Disks x 2 per disk)
regards,
Andy.
"Carrasco" <anonymous@.discussions.microsoft.com> wrote in message
news:498F6762-0B5B-4636-97CC-E04B243A54CE@.microsoft.com...
> HI !
> Does anybody know what is an ideal or problem issue conserning ! Queue I/O
( FOR READ AND WRITE ) ! What number is considered HIGH or normal ! Does
anybody know that ?
> Thank's
>