Running a profile trace in the background

We want to record accurate usage statistics per user of our OLAP database by logging the queries into a database table - the profile trace give us exactly what we want (user ID, domain, duration, CPU time) except it means actively running the app on the server.

Is there not a way to run it in the background as a scheduled job or service (or something) and writing it into a database table for later analysis?

Ultimately we want to use number of queries or CPU time to change the system out to those who are actually using the system. So monitoring accurately is important.

Hi Johannes,

This is an area I'm also looking into.

AS2005's 'Flight Recorder' functionality automatically runs a trace in the background and dumps the results to .trc files on the server (see C:\Program Files\Microsoft SQL Server\MSSQL.x\OLAP\Log) - they may not contain all of the data you're after, but they might well contain most of it. It should then be possible to load this data into a relational table and do some cool stuff with it...

If you (or for that matter anyone else who's reading this) is interested in discussing what the requirements for an app that monitored AS2005 would be, then please drop me an email. My contact details are available at http://www.crossjoin.co.uk/contact.html .

Chris

|||

Try also using the QueryLog server properties: you can setup a connection string to a relational database where queries will be logged. With SQL Management Studio, right click on the AS2005 server item -> Properties -> look for "Log \ QueryLog" entries.

More info here: http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/config_ssas_querylog.mspx

(please note that there is a current issue with the QueryLog: once it has a connection string setup, future runs of the Server Properties dialog will toggle starting/stopping the log; this problem is being fixed in the next SP, meanwhile the work-around is to not run directly the server properties dialog, but to script it and then remove the QueryLog properties - that is for future runs, not for the initial run when you setup the query log for the first time)

Adrian Dumitrascu

Running a local package

Is it possible to make a local package run everytime a record is saved to my
database?
Hi
I am not sure what you mean by local package, but you could run a DTS
package run a trigger, although it may not be advisable. Depending on what
you really want to do, there may be some other method.
John
"johnfli" <john@.here.com> wrote in message
news:u8OqaWu4EHA.1392@.tk2msftngp13.phx.gbl...
> Is it possible to make a local package run everytime a record is saved to
> my
> database?
>
|||I have a DTS package that is currently set every 30 min. that goes through
the database and any fields that has blank fields, it makes them NULL.
I was wondering if there was a way to have it run through and do that every
time a user enters in a new record.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:u2RPDtu4EHA.4028@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Hi
> I am not sure what you mean by local package, but you could run a DTS
> package run a trigger, although it may not be advisable. Depending on what
> you really want to do, there may be some other method.
> John
> "johnfli" <john@.here.com> wrote in message
> news:u8OqaWu4EHA.1392@.tk2msftngp13.phx.gbl...
to
>
|||You could use Check Constraints to disallow empty strings. That would fail
the insert or update. The other option would be to code triggers that would
replace undesireable data.
Sincerely,
Anthony Thomas

"johnfli" <john@.here.com> wrote in message
news:%23%23$JCUv4EHA.2196@.TK2MSFTNGP14.phx.gbl...
I have a DTS package that is currently set every 30 min. that goes through
the database and any fields that has blank fields, it makes them NULL.
I was wondering if there was a way to have it run through and do that every
time a user enters in a new record.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:u2RPDtu4EHA.4028@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Hi
> I am not sure what you mean by local package, but you could run a DTS
> package run a trigger, although it may not be advisable. Depending on what
> you really want to do, there may be some other method.
> John
> "johnfli" <john@.here.com> wrote in message
> news:u8OqaWu4EHA.1392@.tk2msftngp13.phx.gbl...
to
>
|||Hi
I think the best way to do this would be to change the insert statement to
use NULLIF(). Failing that you can use an instead of trigger to do the same
thing.
John
"johnfli" <john@.here.com> wrote in message
news:%23%23$JCUv4EHA.2196@.TK2MSFTNGP14.phx.gbl...
>I have a DTS package that is currently set every 30 min. that goes through
> the database and any fields that has blank fields, it makes them NULL.
> I was wondering if there was a way to have it run through and do that
> every
> time a user enters in a new record.
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:u2RPDtu4EHA.4028@.TK2MSFTNGP15.phx.gbl...
> to
>
sql

Running a local package

Is it possible to make a local package run everytime a record is saved to my
database?Hi
I am not sure what you mean by local package, but you could run a DTS
package run a trigger, although it may not be advisable. Depending on what
you really want to do, there may be some other method.
John
"johnfli" <john@.here.com> wrote in message
news:u8OqaWu4EHA.1392@.tk2msftngp13.phx.gbl...
> Is it possible to make a local package run everytime a record is saved to
> my
> database?
>|||I have a DTS package that is currently set every 30 min. that goes through
the database and any fields that has blank fields, it makes them NULL.
I was wondering if there was a way to have it run through and do that every
time a user enters in a new record.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:u2RPDtu4EHA.4028@.TK2MSFTNGP15.phx.gbl...
> Hi
> I am not sure what you mean by local package, but you could run a DTS
> package run a trigger, although it may not be advisable. Depending on what
> you really want to do, there may be some other method.
> John
> "johnfli" <john@.here.com> wrote in message
> news:u8OqaWu4EHA.1392@.tk2msftngp13.phx.gbl...
> > Is it possible to make a local package run everytime a record is saved
to
> > my
> > database?
> >
> >
>|||You could use Check Constraints to disallow empty strings. That would fail
the insert or update. The other option would be to code triggers that would
replace undesireable data.
Sincerely,
Anthony Thomas
"johnfli" <john@.here.com> wrote in message
news:%23%23$JCUv4EHA.2196@.TK2MSFTNGP14.phx.gbl...
I have a DTS package that is currently set every 30 min. that goes through
the database and any fields that has blank fields, it makes them NULL.
I was wondering if there was a way to have it run through and do that every
time a user enters in a new record.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:u2RPDtu4EHA.4028@.TK2MSFTNGP15.phx.gbl...
> Hi
> I am not sure what you mean by local package, but you could run a DTS
> package run a trigger, although it may not be advisable. Depending on what
> you really want to do, there may be some other method.
> John
> "johnfli" <john@.here.com> wrote in message
> news:u8OqaWu4EHA.1392@.tk2msftngp13.phx.gbl...
> > Is it possible to make a local package run everytime a record is saved
to
> > my
> > database?
> >
> >
>|||Hi
I think the best way to do this would be to change the insert statement to
use NULLIF(). Failing that you can use an instead of trigger to do the same
thing.
John
"johnfli" <john@.here.com> wrote in message
news:%23%23$JCUv4EHA.2196@.TK2MSFTNGP14.phx.gbl...
>I have a DTS package that is currently set every 30 min. that goes through
> the database and any fields that has blank fields, it makes them NULL.
> I was wondering if there was a way to have it run through and do that
> every
> time a user enters in a new record.
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:u2RPDtu4EHA.4028@.TK2MSFTNGP15.phx.gbl...
>> Hi
>> I am not sure what you mean by local package, but you could run a DTS
>> package run a trigger, although it may not be advisable. Depending on
>> what
>> you really want to do, there may be some other method.
>> John
>> "johnfli" <john@.here.com> wrote in message
>> news:u8OqaWu4EHA.1392@.tk2msftngp13.phx.gbl...
>> > Is it possible to make a local package run everytime a record is saved
> to
>> > my
>> > database?
>> >
>> >
>>
>

Run Faster

Hi Guys,
I have SQL file but it run slowly when comes to huge record. How do I make it faster. I do create an index but how to make use the index? Pls help me on this...
Many Thanks,
Regards,
Shaffiqpost the query.let us see ur query :D|||I running it on Coldfusion program :

SELECT ID,
ORDERREFERENCE,
CUSTOMER,
TRANSACTIONTYPE,
FIRSTNAME,
LASTNAME,
COMPANY,
ADDRESS,
CITY,
STATE,
ZIP,
COUNTRY,
EMAIL,
PHONE,
SHIPFIRSTNAME,
SHIPLASTNAME,
SHIPADDRESS,
SHIPCITY,
SHIPSTATE,
SHIPZIP,
SHIPCOUNTRY,
COMMENT1,
COMMENT2
FROM GTRANSACTIONRECORD
WHERE 0 = 0



AND TRANSACTIONDATETIME BETWEEN <CFQUERYPARAM VALUE="2005-02-17 00:00:00" CFSQLTYPE="CF_SQL_DATE">
AND <CFQUERYPARAM VALUE="2005-02-18 00:00:00" CFSQLTYPE="CF_SQL_DATE">


ORDER BY ORDERREFERENCE ASC|||you do not need the "0 = 0" and I would try creating a nonclustered index on TRANSACTIONDATETIME.|||Yeah, drop the 0=0, though that is not what is causing the query to run slowly.
Do you have indexes on TRANSACTIONDATETIME and ORDERREFERENCE?
Do you absolutely need the ORDER BY clause? I'm guessing that is where the slowdown is occuring, and recordset ordering is usually a presentation issue that should be handled by the interface or reporting tool.

Have you tried running this code in Query Analyzer with the SHOW EXECUTION PLAN option set?|||I think he using '0=0' for constructing where clause dynamically in application,so he dont have to check whether its first filter case or second case.|||That would be my guess too, or it was left behind by a previous coder...