Procedure Time out: running

Showing posts with label running. Show all posts

Friday, March 30, 2012

Profiler and performance

I'm planning on running a profiler trace on my DW database. It will run all
night long.
My concern is how can profiler trace effect my databse performance?
Thanks.Sinke
http://www.sql-server-performance.com/sql_server_profiler_tips.asp
"Sinke Pinke" <sinkepinke@.REM_OVEnet.hr> wrote in message
news:e8dchs$539$1@.ss408.t-com.hr...
> I'm planning on running a profiler trace on my DW database. It will run
> all
> night long.
> My concern is how can profiler trace effect my databse performance?
> Thanks.
>|||If you can :
1)Run the Profiler from another machine
2)Be careful in the data you caollect i.e specify events , and use filters,
particuarly the database(s) you wnat to capture data on. Also, you may not
want to capture data of every single event
3)Save the data to a file on your hard disk , rather than a db table
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Sinke Pinke" <sinkepinke@.REM_OVEnet.hr> wrote in message
news:e8dchs$539$1@.ss408.t-com.hr...
> I'm planning on running a profiler trace on my DW database. It will run
all
> night long.
> My concern is how can profiler trace effect my databse performance?
> Thanks.
>sql

Profiler and performance

I'm planning on running a profiler trace on my DW database. It will run all
night long.
My concern is how can profiler trace effect my databse performance?
Thanks.Sinke
http://www.sql-server-performance.c...ofiler_tips.asp
"Sinke Pinke" <sinkepinke@.REM_OVEnet.hr> wrote in message
news:e8dchs$539$1@.ss408.t-com.hr...
> I'm planning on running a profiler trace on my DW database. It will run
> all
> night long.
> My concern is how can profiler trace effect my databse performance?
> Thanks.
>|||If you can :
1)Run the Profiler from another machine
2)Be careful in the data you caollect i.e specify events , and use filters,
particuarly the database(s) you wnat to capture data on. Also, you may not
want to capture data of every single event
3)Save the data to a file on your hard disk , rather than a db table
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Sinke Pinke" <sinkepinke@.REM_OVEnet.hr> wrote in message
news:e8dchs$539$1@.ss408.t-com.hr...
> I'm planning on running a profiler trace on my DW database. It will run
all
> night long.
> My concern is how can profiler trace effect my databse performance?
> Thanks.
>

Profiler and alerts

Is there a way to set up an alert in SQL Server when a long running query
(say 30 seconds or greater) occurs in Profiler?
Message posted via http://www.sqlmonster.com
Robert Richards via SQLMonster.com wrote:
> Is there a way to set up an alert in SQL Server when a long running
> query (say 30 seconds or greater) occurs in Profiler?
Profiler is just a client application. What you can do is write a
server-side trace that monitors SQL:BatchCompleted and RPC:Completed
events with a Duration >= 30,000. Have the trace write to a local file
on the SQL Server box. You can then write a recurring SQL job that
pauses the trace, looks in the trace file for any rows (using
fn_trace_gettable) and if it finds them, inserts the rows into a real
table and sends an alert using xp_sendmail or xp_logevent and
sp_add_alert. Then start up the trace again.
But there might be an easier way... We'll see what the rest of the ng
recommends.
David Gugick
Imceda Software
www.imceda.com
|||This is presently a bit of a pain to do, but the new
Microsoft.SqlServer.Management.Trace library in SQL05 lets you grab sql
trace events on the fly. It's backward compatible to SQL2K too, so you could
experiment with it now for SQL2K. I'm not recommending you stick this into
production (as SQL05's still in beta) but it will at least give you a view
of what's coming up & perhaps help you make a decision on how much time you
want to spend on this presently given the emerging technology.
Here's a vb demo of how it works.
Imports Microsoft.SqlServer.Management.Trace
Dim ts as TraceServer = new TraceServer
Dim c as ConnectionInfoBase = new SqlConnectionInfo("localhost")
c.SqlConnectionInfo.UseIntegratedSecurity = true
ts.InitializeAsReader(c, "c:\Standard.tdf")
do while reader.Read = true
'read trace info off the TraceServer's
'(reader) GetName() & GetValue() methods
loop
ts.Close
ts.Dispose
hth
Regards,
Greg Linwood
SQL Server MVPp
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:O3J0rOiDFHA.4052@.TK2MSFTNGP15.phx.gbl...
> Robert Richards via SQLMonster.com wrote:
> Profiler is just a client application. What you can do is write a
> server-side trace that monitors SQL:BatchCompleted and RPC:Completed
> events with a Duration >= 30,000. Have the trace write to a local file on
> the SQL Server box. You can then write a recurring SQL job that pauses the
> trace, looks in the trace file for any rows (using fn_trace_gettable) and
> if it finds them, inserts the rows into a real table and sends an alert
> using xp_sendmail or xp_logevent and sp_add_alert. Then start up the trace
> again.
> But there might be an easier way... We'll see what the rest of the ng
> recommends.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com

Profiler and alerts

Is there a way to set up an alert in SQL Server when a long running query
(say 30 seconds or greater) occurs in Profiler?
Message posted via http://www.droptable.comRobert Richards via droptable.com wrote:
> Is there a way to set up an alert in SQL Server when a long running
> query (say 30 seconds or greater) occurs in Profiler?
Profiler is just a client application. What you can do is write a
server-side trace that monitors SQL:BatchCompleted and RPC:Completed
events with a Duration >= 30,000. Have the trace write to a local file
on the SQL Server box. You can then write a recurring SQL job that
pauses the trace, looks in the trace file for any rows (using
fn_trace_gettable) and if it finds them, inserts the rows into a real
table and sends an alert using xp_sendmail or xp_logevent and
sp_add_alert. Then start up the trace again.
But there might be an easier way... We'll see what the rest of the ng
recommends.
David Gugick
Imceda Software
www.imceda.com|||This is presently a bit of a pain to do, but the new
Microsoft.SqlServer.Management.Trace library in SQL05 lets you grab sql
trace events on the fly. It's backward compatible to SQL2K too, so you could
experiment with it now for SQL2K. I'm not recommending you stick this into
production (as SQL05's still in beta) but it will at least give you a view
of what's coming up & perhaps help you make a decision on how much time you
want to spend on this presently given the emerging technology.
Here's a vb demo of how it works.
Imports Microsoft.SqlServer.Management.Trace
Dim ts as TraceServer = new TraceServer
Dim c as ConnectionInfoBase = new SqlConnectionInfo("localhost")
c.SqlConnectionInfo.UseIntegratedSecurity = true
ts.InitializeAsReader(c, "c:\Standard.tdf")
do while reader.Read = true
'read trace info off the TraceServer's
'(reader) GetName() & GetValue() methods
loop
ts.Close
ts.Dispose
hth
Regards,
Greg Linwood
SQL Server MVPp
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:O3J0rOiDFHA.4052@.TK2MSFTNGP15.phx.gbl...
> Robert Richards via droptable.com wrote:
> Profiler is just a client application. What you can do is write a
> server-side trace that monitors SQL:BatchCompleted and RPC:Completed
> events with a Duration >= 30,000. Have the trace write to a local file on
> the SQL Server box. You can then write a recurring SQL job that pauses the
> trace, looks in the trace file for any rows (using fn_trace_gettable) and
> if it finds them, inserts the rows into a real table and sends an alert
> using xp_sendmail or xp_logevent and sp_add_alert. Then start up the trace
> again.
> But there might be an easier way... We'll see what the rest of the ng
> recommends.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com

Profiler and alerts

Is there a way to set up an alert in SQL Server when a long running query
(say 30 seconds or greater) occurs in Profiler?
--
Message posted via http://www.sqlmonster.comRobert Richards via SQLMonster.com wrote:
> Is there a way to set up an alert in SQL Server when a long running
> query (say 30 seconds or greater) occurs in Profiler?
Profiler is just a client application. What you can do is write a
server-side trace that monitors SQL:BatchCompleted and RPC:Completed
events with a Duration >= 30,000. Have the trace write to a local file
on the SQL Server box. You can then write a recurring SQL job that
pauses the trace, looks in the trace file for any rows (using
fn_trace_gettable) and if it finds them, inserts the rows into a real
table and sends an alert using xp_sendmail or xp_logevent and
sp_add_alert. Then start up the trace again.
But there might be an easier way... We'll see what the rest of the ng
recommends.
David Gugick
Imceda Software
www.imceda.com|||This is presently a bit of a pain to do, but the new
Microsoft.SqlServer.Management.Trace library in SQL05 lets you grab sql
trace events on the fly. It's backward compatible to SQL2K too, so you could
experiment with it now for SQL2K. I'm not recommending you stick this into
production (as SQL05's still in beta) but it will at least give you a view
of what's coming up & perhaps help you make a decision on how much time you
want to spend on this presently given the emerging technology.
Here's a vb demo of how it works.
Imports Microsoft.SqlServer.Management.Trace
Dim ts as TraceServer = new TraceServer
Dim c as ConnectionInfoBase = new SqlConnectionInfo("localhost")
c.SqlConnectionInfo.UseIntegratedSecurity = true
ts.InitializeAsReader(c, "c:\Standard.tdf")
do while reader.Read = true
'read trace info off the TraceServer's
'(reader) GetName() & GetValue() methods
loop
ts.Close
ts.Dispose
hth
Regards,
Greg Linwood
SQL Server MVPp
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:O3J0rOiDFHA.4052@.TK2MSFTNGP15.phx.gbl...
> Robert Richards via SQLMonster.com wrote:
>> Is there a way to set up an alert in SQL Server when a long running
>> query (say 30 seconds or greater) occurs in Profiler?
> Profiler is just a client application. What you can do is write a
> server-side trace that monitors SQL:BatchCompleted and RPC:Completed
> events with a Duration >= 30,000. Have the trace write to a local file on
> the SQL Server box. You can then write a recurring SQL job that pauses the
> trace, looks in the trace file for any rows (using fn_trace_gettable) and
> if it finds them, inserts the rows into a real table and sends an alert
> using xp_sendmail or xp_logevent and sp_add_alert. Then start up the trace
> again.
> But there might be an easier way... We'll see what the rest of the ng
> recommends.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com

Profiler 2000

I set up an audit trace while logged on to the server. After running it
for a while I logged out and found that the trace had stopped and
closed. I really had wanted to let it run for a couple of days -- we
are trying to figure out a SQL injection attack that defaced a website
this week. I am guessing that this will probably require running it
from a monitoring workstation while logged on, but is there perhaps a
way to leave a trace running while not logged on, whether on the server
or not? (because of other considerations w/ folder redirection and
profiles I can't leave the WS running overnight). BOL didn't help
Graham (Pete) Berry
PeteBerry@.Caltech.edu
Don't use profiler, use Trace instead. Profiler is just a gui that uses the
built in TRACE functionality. You can use profiler to setup the events and
columns you wish to trace. Then start and immediately stop the trace. Then
go to the menu under File and then Script Trace. This will give you a
template on how to create a file that holds the trace event data by filling
in the missing info as shown in the header of the file. When you run this
it will create a trace that runs independently of that connection so you can
log off and it will still run. See BOL for more details.
Andrew J. Kelly SQL MVP
"Pete Berry" <PeteBerry@.Caltech.edu> wrote in message
news:MPG.210fe7ec4cfeafbc989699@.msnews.microsoft.c om...
>I set up an audit trace while logged on to the server. After running it
> for a while I logged out and found that the trace had stopped and
> closed. I really had wanted to let it run for a couple of days -- we
> are trying to figure out a SQL injection attack that defaced a website
> this week. I am guessing that this will probably require running it
> from a monitoring workstation while logged on, but is there perhaps a
> way to leave a trace running while not logged on, whether on the server
> or not? (because of other considerations w/ folder redirection and
> profiles I can't leave the WS running overnight). BOL didn't help
> --
> Graham (Pete) Berry
> PeteBerry@.Caltech.edu
|||Hi Pete,
You can use the following system SP's. This approach, however, requires a
bit of work.
sp_trace_create
sp_trace_generateevent
sp_trace_setevent
sp_trace_setfilter
sp_trace_setstatus
fn_trace_gettable
You can also try to use SQL Trace Analyzer developed by Lakeside SQL which
you can download from here:
http://www.lakesidesql.com/TaDownload/ToTest/1.1.2007.813/TASetup.zip. Trace
scheduling is just a supplementary feature of the aplication. It provides
very granular trace filtering which can help you find what you're looking
for.
HTH.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:Ow%23m9eizHHA.484@.TK2MSFTNGP06.phx.gbl...
> Don't use profiler, use Trace instead. Profiler is just a gui that uses
> the built in TRACE functionality. You can use profiler to setup the events
> and columns you wish to trace. Then start and immediately stop the trace.
> Then go to the menu under File and then Script Trace. This will give you
> a template on how to create a file that holds the trace event data by
> filling in the missing info as shown in the header of the file. When you
> run this it will create a trace that runs independently of that connection
> so you can log off and it will still run. See BOL for more details.
> --
> Andrew J. Kelly SQL MVP
> "Pete Berry" <PeteBerry@.Caltech.edu> wrote in message
> news:MPG.210fe7ec4cfeafbc989699@.msnews.microsoft.c om...
>

Profiler / sql trace

Hi Michael,
I'm very familiar about sp_lock/sp_who. My main confusion
is that MS PSS strongly is against running Profiler at the
client side everytime I've talked/worked with them. If the
Proiler is so bad, why MS ship it at all ! As I've said
they have always run sql trace procs at our sql server
box. Any comments ?
thanks
long

quote:

>--Original Message--
>Hello,
>My name is Michael and I would like to thank you for

using Microsoft

quote:

>newsgroup.
>Based on my experience, this kind of problem does not

have an easy and

quote:

>clear answer. It is hard for me to tell you which the

best way is to

quote:

>capture/view sql activities; what a good method is for

you may not be a

quote:

>good method for other people. The way in which we choose

to monitor SQL

quote:

>Server depends on specific and particular concerns. I

can help you find

quote:

>the most appropriate way for you to monitor SQL server

based on your needs,

quote:

>but, please understand that there is no "best way" to do

this.

quote:

>When people use stored procedures to monitor SQL Server,

the information we

quote:

>are able to get is limited. For example:
>Sp_who stored procedure provides information about

current Microsoft SQL

quote:

>Server users and processes.
>sp_lock stored procedure reports information about locks.
>SQL Server Profiler (SQL Trace) is a graphical tool that

allows system

quote:

>administrators and database developers to monitor engine

events on

quote:

>computers running Microsoft SQL Server. Using SQL Server

Profiler on the

quote:

>client machine to monitor SQL Server will increase

network traffic.

quote:

>Monitoring on the server will increase the workload of

the server machine.

quote:

>For more information regarding this issue, please refer

to the following

quote:

>article.
>325263 Support WebCast: SQL Server 2000 Profiler: What's

New and How to

quote:

>http://support.microsoft.com/?id=325263
>I hope the above explanation is clear. Please let us know

if you need

quote:

>further assistance on this issue.
>Regards,
>Michael Shao
>Microsoft Online Partner Support
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and

confers no rights.

quote:

>.
>

Hi Long,
Thanks for your feedback. Generally, the way we choose to monitor SQL
Server depends on the specific objective. Based on the particular
situation, our engineers will choose the most appropriate method. As for
this issue, can you please give me a sample to help describe the specific
objective? That way, we can help you analyze and determine which the most
appropriate way is to monitor SQL Server.
I am looking forward to hearing from you soon.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Hi Long,
Thank you for choosing Microsoft! Michael is on holiday and I'm his backup.
My name is
Billy and it's my pleasure to assist you with this issue.
To start with, as we known, Microsoft sometimes recommends our customers to
perform the
best practices for their benefits. However, we never prohibit customers from
applying their
easier way to do anything. It all depends on customer's specific issues and
detailed
scenarios.
Based on my experience, the main reason why you are strongly recommended to
run the
Profiler at the server side is that it guarantees you can trace all the info
rmation.
In the GUI trace setup, you can click the checkbox for: "server processes sq
l server trace
data"
The stored procedures to create/exec profiler traces will guarantee events w
ill not be
dropped unless you use: sp_create_trace @.options = 1 which instead of a file
creates a
rowset.
Server side trace files guarantee events not to be dropped, however, result
set (or rowset
cannot guarantee this. On the client side, you cannot ensure the trace infor
mation is
complete as SQL Profiler gets the trace as a result set to the client.
Another consideration is the trace performance. On the client side, the trac
e information is
received as a result set, in which case if there is a large volume of trace
information, the
performance will definitely be poorer than tracing on the server side. That'
s why we
recommend you run the Profiler on the server side.
However, if trace information is complete and the performance is also ok on
the client side. I
fully understand that running the Profiler is convenient and also efficient
for you in some
situations (such as the server is not on hand). Do you agree with me Long?
If there is anything more we can still do to assist you, please don't hesita
te to let us know.
We are here to be of assistance!
Best regards,
Billy Yao
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

Wednesday, March 28, 2012

Profiler

How big is the performance penalty for running profiler?The best phrase I've heard is "you pay as you go".
In other words, if you have one row and lots of columns in profiler for
every page-lock event, then the hit will be significant. On the other hand,
if you only trace infrequent events, and capture few columns, like say,
login/logout, then the penalty will be negligable.
--
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"Andrew" <a@.b.com> wrote in message
news:085901c38cdb$e56df160$a001280a@.phx.gbl...
> How big is the performance penalty for running profiler?

Profiler

Running profiler to catch anything on the server that has
a duration of greater than 30,000 miliseconds(30 sec),
and realisticly we shouldn't have much logged, but in 3
hours we have over 23,000 rows logged and most of the
rows text data is recording this:
-- network protocol: TCP/IP
set quoted_identifier on
set implicit_transactions off
set cursor_close_on_commit off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set language us_english
set dateformat mdy
set datefirst 7
What does this mean? And why is this running so long?
Thanks!That is most likely coming from the Logout event. Remove that event from
the trace definition and it should go away.
Andrew J. Kelly SQL MVP
"anita" <anonymous@.discussions.microsoft.com> wrote in message
news:0d8c01c519dc$12c9c600$a601280a@.phx.gbl...
> Running profiler to catch anything on the server that has
> a duration of greater than 30,000 miliseconds(30 sec),
> and realisticly we shouldn't have much logged, but in 3
> hours we have over 23,000 rows logged and most of the
> rows text data is recording this:
> -- network protocol: TCP/IP
> set quoted_identifier on
> set implicit_transactions off
> set cursor_close_on_commit off
> set ansi_warnings on
> set ansi_padding on
> set ansi_nulls on
> set concat_null_yields_null on
> set language us_english
> set dateformat mdy
> set datefirst 7
> What does this mean? And why is this running so long?
> Thanks!
>|||See the following topics in Books Online:
Set Options
User Options;
user options Option
Use the user options option to specify global defaults for all users. A
list of default query processing options is established for the duration of
a user's work session. user options allows you to change the default values
of the SET options (if the server's default settings are not appropriate).
A user can override these defaults by using the SET statement. You can
configure user options dynamically for new logins. After you change the
setting of user options, new logins use the new setting; current logins are
not affected.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Professional edition vs. SQL Server

Hi
i am using the professional edition (since i'm running it on a windows 2000 pro machine), but i wanted to know what performance gains there are between the pro. edition vs. the real sql server that would be run on a windows 2000 advanced server machine
currently i'm trying to write/query an enormous amount of data. the bottleneck is now the database, but before i go through the trouble of rebuilding a maching with 2000 server, i wanted to know what the "gains" were and if it would even be worth the time to do this
Thanks
mikeOOPS... i meant PERSONAL and not professional edition.|||Personal Edition has the same performance delimiter as MSDE, i.e. 25
concurrent users is really pushing it. It has no limit on the database size
though, but you need a full SQL Server license to actually be allowed to use
Personal Edition on a separate machine. You can't use personal Edition
separately. I.e. what you are doing probably violates the licensing
agreement.
--
Jacco Schalkwijk
SQL Server MVP
"mike" <anonymous@.discussions.microsoft.com> wrote in message
news:23137F31-435A-4FF0-9AAB-9ACC5190E48B@.microsoft.com...
> OOPS... i meant PERSONAL and not professional edition.

Production Report against OLAP database

We have a large number of (Crystal) Reports ( based on Stor Proc ) running
against our OLTP database (SQL Server).
Instead of reports running against OLTP database I would like to create
Cubes and create production reports against them.
Is it possible to do that? Are there any client applications which allows to
create these types of reports? We don't need real time information.
Any help is greatly appreciated."Atul Mehta" <amehta@.oaktreecap.com> schrieb im Newsbeitrag
news:ub8DdHCvDHA.1740@.TK2MSFTNGP12.phx.gbl...

quote:

> We have a large number of (Crystal) Reports ( based on Stor Proc ) running
> against our OLTP database (SQL Server).
> Instead of reports running against OLTP database I would like to create
> Cubes and create production reports against them.
> Is it possible to do that? Are there any client applications which allows

quote:

> create these types of reports? We don't need real time information.
> Any help is greatly appreciated.

Atul, you don't necessarily need another Reporting Tool. You need to decide
where you want to build your -> Data Warehouse or -> Data Mart in.
Regards,
Joerg|||If you're looking for cheap and easy, office web components (pivot tables),
or cheap and tedious: ASP web pages and PTS. You might want to see if MSFT
Reporting Services is a potential solution for you.
Another option is to build a star schema reporting database, which will be
useful for building cubes as well, but may eliminate the need.
"Atul Mehta" <amehta@.oaktreecap.com> wrote in message
news:ub8DdHCvDHA.1740@.TK2MSFTNGP12.phx.gbl...

quote:

> We have a large number of (Crystal) Reports ( based on Stor Proc ) running
> against our OLTP database (SQL Server).
> Instead of reports running against OLTP database I would like to create
> Cubes and create production reports against them.
> Is it possible to do that? Are there any client applications which allows

quote:

> create these types of reports? We don't need real time information.
> Any help is greatly appreciated.
>
>
>

production or test server...or both

Hi, We currently have SQL2000 running in a production environment with 2 commercial applications. I would like to use the same server to upsize some access applications, test and develope new internal applications. I have been warned by our IS dept, however, that I should not have any test applications on a production server as I run the risk of closing down/screwing up the performance the server (looping queries...etc) through the test applications.
They say I should buy another server, set alone as a test server, and only add the internal applications to the production server after thorough testing.
Of course , our IS dept offers no test or production server for our dept to use - so this gets pretty expensive for us.
My question - can I do both on one server or not?
thanks!
JeffIMHO I agree with your IS dept 100%. Now if your Production box has ample resources and your Production apps can stand some interference or down time due to a screw ups then why not.

BIG DISCALMER!! I can guarantee you that at some point someone will run something that will drag the system down or even (gulp) bring the server down. Just like your IS guys have pointed out.

In some shops the above would be unacceptable, maybe not in yours. You sort of have to way all the pros and cons. Would a low end server really cost that much considering the potential problem?|||RE: They say I should buy another server, set alone as a test server, and only add the internal applications to the production server after thorough testing. Of course , our IS dept offers no test or production server for our dept to use - so this gets pretty expensive for us.

Q1 Why not simply buy and use a low end workstation pc (to lower the costs install Win 2k Pro or XP) with the dev version (or even the 120 day eval) of Sql Server. One ought to be able to easily create that kind of test server for under $1K.

Q2 If $1K is too much, how about making some poor user's workstation (say yours) a dual boot OS box (install the test platform on the new install)? If you have test workstations and you are the one doing the testing you wouldn't need your workstation while you are doing tests anyway, or would you?|||Be very careful of installing evaluation editions of anything by microsoft. I did this with sql server version 7 and had to reinstall which destroyed everything - according to documentation ms has gotten better - but do so at your own risk. Anyway, a better solution is to use msde 1 or 2000 depending on what your solution requires. The following is a link that tells you how to get msde ...

link (http://support.microsoft.com/default.aspx?scid=KB;en-us;324998&)|||Oh - to answer your question - of course you can install anything on the production server - the question is should you. Some people will debate that it is ok to have a test instance with a production instance. However, you can have so many issues with this design. IMO - You use the production for production only - no matter how well stacked your machine is - I have seen enough problems (like memory leaks, over enthusiastic sql statements ...) - Just get a cheapy peepy machine and install want you need - find free software or existing licenses so you have no/minimal costs with the software part.|||RE: Be very careful of installing evaluation editions of anything by microsoft. I did this with sql server version 7 and had to reinstall which destroyed everything - according to documentation ms has gotten better - but do so at your own risk.

Note: The low cost possibility (using a 120 day eval edition) was explicitly made in reference to use on a test server (as a means to further lower total test server costs).

RE: Anyway, a better solution is to use msde 1 or 2000 depending on what your solution requires.

NOTE: MSDE (and Desktop Sql Server editions) do have some intentional limitations built in. As long as you do not need to test any non-MSDE functionality, e.g. (DBs <= 2GB), MSDE is a good general functionality test environment (otherwise, the Sql Server dev. version is often "best", at a fairly reasonable cost). Accordingly, unless specifically deploying to MSDE, avoid using MSDE for any scalability / load testing purposes (or as a substitute for valid Sql Server product performance comparisons).|||MSDE is fully compatible with sql server - so once you have the money to buy that developers edition - no problems mate. Obviously, if you have the $500, then buy the developers edition. Even further - here is a quote from microsoft regarding msde:

"An attractive alternative to using the Microsoft Jet database, MSDE 2000 is designed primarily to provide a low-cost option for developers who need a database server that can be easily distributed and installed with a value-added business solution. Because it is fully compatible with other editions of SQL Server, developers can easily target both SQL Server and MSDE 2000 with the same core code base. This provides a seamless upgrade path from MSDE 2000 to SQL Server if an application grows beyond the storage and scalability limits of MSDE 2000."

MSDE is free without the headaches of the evaluation edition - a no brainer.

As far as the limitations - for the purpose of upsizing from access - this is no problem - even with the size limitation, since it is the same as access. The following article goes into depth about this:

article (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acmsdeop.asp)|||MSDE makes for a good general functionality test environment.

MSDE also often serves a variety of production purposes very well, (even though MSDE does have intentional limitations built in). However, MSDE is a licensed use product with licensed use requirements.

Some information (from MS) on some MSDE limitations: MSDE was TUNED { - "TUNED" may be regarded as a synonym for "CONSTRAINED" in this case - } to deliver the same performance you'd get using the SQL Server enterprise engine AT UP TO FIVE CONCURRENT USERS, and supports up to 2 gigabytes (GB) of data for any individual database. The storage limit is per database, not per server. A single MSDE server can support multiple MSDE databases, each containing up to the 2-GB limit. For more users, or when you need more data storage, you should use SQL Server or SQL Server Enterprise Edition for optimal performance at this higher level of scalability.

Unlike SQL Server, MSDE can't be a replication publisher (although it can act as a replication subscriber) in a transactional replication environment. Also, note that replication of MSDE databases is only possible if SQL Server Client Access Licenses (CALs) are purchased. If you require replication publishing, you'll want to use SQL Server Desktop rather than MSDE. (A CAL is also required for SQL Server Desktop.)

Some information (from MS) on products that permit licensed "free" use of MSDE:

Products that enable use and redistribution of MSDE:
SQL Server 2000 (Developer, Standard, and Enterprise Editions)
Visual Studio .NET (Architect, Developer, and Professional Editions)
ASP.NET Web Matrix Tool
Office XP Developer Edition
MSDN Universal and Enterprise subscriptions

Products that enable use but not redistribution:
Microsoft Access

Some other possible considerations: (see http://www.microsoft.com/sql/howtobuy/msdeuse.asp)
Using MSDE does not reduce or eliminate the need for CALs when interacting with SQL Server 2000 Standard Edition or SQL Server 2000 Enterprise Edition in a production environment.

Q. Can I use SQL Server tools and services in conjunction with MSDE?
A. You can only use SQL Server tools and services in conjunction with MSDE if you acquired MSDE via SQL Server 2000 (any edition) or if you are using MSDE in conjunction with a properly licensed copy of SQL Server 2000. Visit the How to Buy page to obtain a valid SQL Server license.

The following utilities are installed by the MSDE setup application and are provided without restrictions for use with the copy of MSDE that is installed by your application: bcp.exe, cnfgsvr.exe, dcomscm.exe, osql.exe, sqlmangr.exe, scm.exe, sqladhlp.exe, and svrnetcn.exe. The dtsrun.exe utility is also provided, but may not be used during development.

Note: The following Visual Studio family of products also include the right to use and redistribute MSDE in this scenario: Microsoft Visual Basic .NET, Visual C# .NET, Visual C++ .NET, and Visual J# .NET.

Several Microsoft product licenses convey the right to use and redistribute the Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) version 2.0. In all cases, you should refer to the End User License Agreement (EULA) for a full statement of the rights conveyed by your product license.

Monday, March 26, 2012

Produciton SQL CPU running around 50%

Our production SQL Server's CPU runs continuously at about
50%. Below is a copy of the running Process threads, taken
from SQL Spotlight. Can anyone help identify the
bottleneck and give recommendations for improvement
(besides improving queries)' Your help is greatly
appreciated!
Instance Thread ID % CPU % User % Kernel
Elapsed time Switches/sec Thread State
Thread Wait Reason
5 268 104.65 0 106.32 19d 10hrs 207
921.65 Waiting Waiting for a user request
0 0 52.92 0 52.92 147417d 19hrs
807.51 Running -
1 0 50.18 0 50.18 147417d 19hrs
471.7 Running -
3 0 48.15 0 48.15 147417d 19hrs
495.51 Running -
6 272 40.76 0 42.91 19d 10hrs 0
Waiting Waiting for a user request
2 0 36.47 0 36.47 147417d 19hrs
710.07 Running -
15 10268 3 594.66 405.72 3 188.94 9d
20hrs 215 661.8 Waiting Waiting for a user
request
5 344 1.91 0.36 1.55 19d 10hrs 1
134.55 Waiting Waiting for a user request
1 252 1 906.93 335.88 1 571.05 19d
10hrs 229 163.43 Waiting Waiting for an LPC
Receive notice
4 204 1 830.41 266.39 1 564.01 19d
10hrs 133 979.34 Waiting Waiting for an LPC
Receive notice
8 488 1 810.26 278.07 1 532.19 19d
10hrs 0 Waiting Waiting for an LPC Receive
notice
11 4196 1 683.68 288.32 1 395.36 17d
17hrs 197 806.72 Waiting Waiting for an LPC
Receive notice
12 1408 1 612.76 281.77 1 331 17d
17hrs 163 851.13 Waiting Waiting for an LPC
Receive notice
13 4264 1 596.43 286.41 1 310.02 17d
17hrs 135 660.06 Waiting Waiting for an LPC
Receive notice
0 11584 0.79 0.79 0 1d 6hrs
20.82 Waiting Waiting for a user request
4 276 0.36 0 0.36 19d 10hrs
58.45 Waiting Waiting for a user request
3 228 0.12 0 0.12 19d 10hrs 9
Waiting Waiting for an LPC Receive notice
10 2756 0.12 0 0.12 19d 8hrs
2.67 Waiting Waiting for a user requestCome and check ours. It is running at 90% most of the time.
CPU usage depends on the transactions occuring in the
Server (Unless there are other applications running on the
server). I wouldn't be too concern about it at 50%. If you
are, use the SQL Profiler to find out what processes using
the most CPU usage(the number on Profiler is in
miliseconds).
>--Original Message--
>Our production SQL Server's CPU runs continuously at
about
>50%. Below is a copy of the running Process threads,
taken
>from SQL Spotlight. Can anyone help identify the
>bottleneck and give recommendations for improvement
>(besides improving queries)' Your help is greatly
>appreciated!
>Instance Thread ID % CPU % User % Kernel
> Elapsed time Switches/sec Thread State
> Thread Wait Reason
>5 268 104.65 0 106.32 19d 10hrs 207
> 921.65 Waiting Waiting for a user request
>0 0 52.92 0 52.92 147417d 19hrs
> 807.51 Running -
>1 0 50.18 0 50.18 147417d 19hrs
> 471.7 Running -
>3 0 48.15 0 48.15 147417d 19hrs
> 495.51 Running -
>6 272 40.76 0 42.91 19d 10hrs 0
> Waiting Waiting for a user request
>2 0 36.47 0 36.47 147417d 19hrs
> 710.07 Running -
>15 10268 3 594.66 405.72 3 188.94 9d
> 20hrs 215 661.8 Waiting Waiting for a user
>request
>5 344 1.91 0.36 1.55 19d 10hrs 1
> 134.55 Waiting Waiting for a user request
>1 252 1 906.93 335.88 1 571.05 19d
> 10hrs 229 163.43 Waiting Waiting for an LPC
>Receive notice
>4 204 1 830.41 266.39 1 564.01 19d
> 10hrs 133 979.34 Waiting Waiting for an LPC
>Receive notice
>8 488 1 810.26 278.07 1 532.19 19d
> 10hrs 0 Waiting Waiting for an LPC Receive
>notice
>11 4196 1 683.68 288.32 1 395.36 17d
> 17hrs 197 806.72 Waiting Waiting for an LPC
>Receive notice
>12 1408 1 612.76 281.77 1 331 17d
> 17hrs 163 851.13 Waiting Waiting for an LPC
>Receive notice
>13 4264 1 596.43 286.41 1 310.02 17d
> 17hrs 135 660.06 Waiting Waiting for an LPC
>Receive notice
>0 11584 0.79 0.79 0 1d 6hrs
> 20.82 Waiting Waiting for a user request
>4 276 0.36 0 0.36 19d 10hrs
> 58.45 Waiting Waiting for a user request
>3 228 0.12 0 0.12 19d 10hrs 9
> Waiting Waiting for an LPC Receive notice
>10 2756 0.12 0 0.12 19d 8hrs
> 2.67 Waiting Waiting for a user request
>
>.
>|||However, low CPU usage can be because the machine is waiting for I/O requests a lot. Tuning the
queries, by for instance adding indexes so less I/O is used can be a real benefit in these cases.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"James" <anonymous@.discussions.microsoft.com> wrote in message
news:6e2901c48392$136f9480$a401280a@.phx.gbl...
> Come and check ours. It is running at 90% most of the time.
> CPU usage depends on the transactions occuring in the
> Server (Unless there are other applications running on the
> server). I wouldn't be too concern about it at 50%. If you
> are, use the SQL Profiler to find out what processes using
> the most CPU usage(the number on Profiler is in
> miliseconds).
>
> >--Original Message--
> >Our production SQL Server's CPU runs continuously at
> about
> >50%. Below is a copy of the running Process threads,
> taken
> >from SQL Spotlight. Can anyone help identify the
> >bottleneck and give recommendations for improvement
> >(besides improving queries)' Your help is greatly
> >appreciated!
> >
> >Instance Thread ID % CPU % User % Kernel
> > Elapsed time Switches/sec Thread State
> > Thread Wait Reason
> >5 268 104.65 0 106.32 19d 10hrs 207
> > 921.65 Waiting Waiting for a user request
> >
> >0 0 52.92 0 52.92 147417d 19hrs
> > 807.51 Running -
> >1 0 50.18 0 50.18 147417d 19hrs
> > 471.7 Running -
> >3 0 48.15 0 48.15 147417d 19hrs
> > 495.51 Running -
> >6 272 40.76 0 42.91 19d 10hrs 0
> > Waiting Waiting for a user request
> >
> >2 0 36.47 0 36.47 147417d 19hrs
> > 710.07 Running -
> >15 10268 3 594.66 405.72 3 188.94 9d
> > 20hrs 215 661.8 Waiting Waiting for a user
> >request
> >5 344 1.91 0.36 1.55 19d 10hrs 1
> > 134.55 Waiting Waiting for a user request
> >
> >1 252 1 906.93 335.88 1 571.05 19d
> > 10hrs 229 163.43 Waiting Waiting for an LPC
> >Receive notice
> >4 204 1 830.41 266.39 1 564.01 19d
> > 10hrs 133 979.34 Waiting Waiting for an LPC
> >Receive notice
> >8 488 1 810.26 278.07 1 532.19 19d
> > 10hrs 0 Waiting Waiting for an LPC Receive
> >notice
> >11 4196 1 683.68 288.32 1 395.36 17d
> > 17hrs 197 806.72 Waiting Waiting for an LPC
> >Receive notice
> >12 1408 1 612.76 281.77 1 331 17d
> > 17hrs 163 851.13 Waiting Waiting for an LPC
> >Receive notice
> >13 4264 1 596.43 286.41 1 310.02 17d
> > 17hrs 135 660.06 Waiting Waiting for an LPC
> >Receive notice
> >0 11584 0.79 0.79 0 1d 6hrs
> > 20.82 Waiting Waiting for a user request
> >
> >4 276 0.36 0 0.36 19d 10hrs
> > 58.45 Waiting Waiting for a user request
> >
> >3 228 0.12 0 0.12 19d 10hrs 9
> > Waiting Waiting for an LPC Receive notice
> >
> >10 2756 0.12 0 0.12 19d 8hrs
> > 2.67 Waiting Waiting for a user request
> >
> >
> >.
> >

Friday, March 23, 2012

processors replacement

Hi,
SQL Server 7.0 is running on WinNT 4.0, 4 processors 4G
memory (3G used for sql server). We're going to replace
these processors with the new, faster 4 processors. Is
there any known problem we can expect regarding starting
sql services and databases with new processors?
TIA
Arminarmin,
As long as Windows comes up, should be fine. ;-)
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
armin wrote:
> Hi,
> SQL Server 7.0 is running on WinNT 4.0, 4 processors 4G
> memory (3G used for sql server). We're going to replace
> these processors with the new, faster 4 processors. Is
> there any known problem we can expect regarding starting
> sql services and databases with new processors?
> TIA
> Armin|||Your execution plans are going to need updating. They are
developed using a combination of cpu, memory and disk
speed, so if one changes its going to effect the rest.
Its not a big job, just look up update statistics, then
watch your server fly...
Peter
"The best minds are not in government. If any were,
business would steal them away."
Ronald Reagan
>--Original Message--
>Hi,
>SQL Server 7.0 is running on WinNT 4.0, 4 processors 4G
>memory (3G used for sql server). We're going to replace
>these processors with the new, faster 4 processors. Is
>there any known problem we can expect regarding starting
>sql services and databases with new processors?
>TIA
>Armin
>.
>|||They have to shut down the server to add the upgrades and all execution
plans are lost when you stop sql server anyway. And the plans are not
directly related to disk speed at all. Even CPU and memory are things that
are taken into account at run time and not necessarily at compile time. A
complex plan will always have a single and a parallel plan created the first
time it is run. At run time it is decided based on how busy the cpu's are,
memory available, etc that determine if the single threaded plan is used or
a parallel plan and how many threads generated.
--
Andrew J. Kelly SQL MVP
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:766e01c494e9$304f2300$a301280a@.phx.gbl...
> Your execution plans are going to need updating. They are
> developed using a combination of cpu, memory and disk
> speed, so if one changes its going to effect the rest.
> Its not a big job, just look up update statistics, then
> watch your server fly...
> Peter
> "The best minds are not in government. If any were,
> business would steal them away."
> Ronald Reagan
>
> >--Original Message--
> >Hi,
> >
> >SQL Server 7.0 is running on WinNT 4.0, 4 processors 4G
> >memory (3G used for sql server). We're going to replace
> >these processors with the new, faster 4 processors. Is
> >there any known problem we can expect regarding starting
> >sql services and databases with new processors?
> >
> >TIA
> >
> >Armin
> >.
> >|||Thank you very much, to all of you!
If I unterstood complete discussion correctly, as soon as
NT comes up, I should let it fly?
BR
Armin
>--Original Message--
>armin,
>As long as Windows comes up, should be fine. ;-)
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602m.html
>
>armin wrote:
>> Hi,
>> SQL Server 7.0 is running on WinNT 4.0, 4 processors 4G
>> memory (3G used for sql server). We're going to replace
>> these processors with the new, faster 4 processors. Is
>> there any known problem we can expect regarding
starting
>> sql services and databases with new processors?
>> TIA
>> Armin
>.
>sql

Wednesday, March 21, 2012

Processor Utilization

Hi
I am running SQL Server 2000 on a 2 processor system (Windows 2003
Standard). I regularily run large queries. When I run the queries the CPU
utilization is consitantly around 11%.
It appears as if something is limiting SQL Server to 11% CPU per
process/query. I have verified that its not I/O bound.
The Processor properties are set as follows:
- All 2 processors are checked in Processor Control
- "Maximum worker threads" is set to 255
- "Boost SQL Server priority on Windows" is not checked
- "Use Windows NT fibres" is checked
- "Use all available processors" is checked
Why is the CPU not fully utilized? What does "Boost SQL Server priority on
Windows" do?
Any assistance would be greatly appreciated
Thanks
Hi
You may want to check the boost priority option and not use fibres (as
fibres can cause other problems). You could also try using the MAXDOP hint to
only use one processor.
Monitor context switches to see if there is an excessive number before
considering using fibres. Ken Hendersons book The Gurus Guide to SQL Server
Architecture and Internals ISBN 0201700476 has a good section on this.
John
"Macisu" wrote:

> Hi
> I am running SQL Server 2000 on a 2 processor system (Windows 2003
> Standard). I regularily run large queries. When I run the queries the CPU
> utilization is consitantly around 11%.
> It appears as if something is limiting SQL Server to 11% CPU per
> process/query. I have verified that its not I/O bound.
> The Processor properties are set as follows:
> - All 2 processors are checked in Processor Control
> - "Maximum worker threads" is set to 255
> - "Boost SQL Server priority on Windows" is not checked
> - "Use Windows NT fibres" is checked
> - "Use all available processors" is checked
> Why is the CPU not fully utilized? What does "Boost SQL Server priority on
> Windows" do?
> Any assistance would be greatly appreciated
> Thanks

Processor Utilization

Hi
I am running SQL Server 2000 on a 2 processor system (Windows 2003
Standard). I regularily run large queries. When I run the queries the CPU
utilization is consitantly around 11%.
It appears as if something is limiting SQL Server to 11% CPU per
process/query. I have verified that its not I/O bound.
The Processor properties are set as follows:
- All 2 processors are checked in Processor Control
- "Maximum worker threads" is set to 255
- "Boost SQL Server priority on Windows" is not checked
- "Use Windows NT fibres" is checked
- "Use all available processors" is checked
Why is the CPU not fully utilized? What does "Boost SQL Server priority on
Windows" do?
Any assistance would be greatly appreciated
ThanksHi
You may want to check the boost priority option and not use fibres (as
fibres can cause other problems). You could also try using the MAXDOP hint to
only use one processor.
Monitor context switches to see if there is an excessive number before
considering using fibres. Ken Hendersons book The Gurus Guide to SQL Server
Architecture and Internals ISBN 0201700476 has a good section on this.
John
"Macisu" wrote:
> Hi
> I am running SQL Server 2000 on a 2 processor system (Windows 2003
> Standard). I regularily run large queries. When I run the queries the CPU
> utilization is consitantly around 11%.
> It appears as if something is limiting SQL Server to 11% CPU per
> process/query. I have verified that its not I/O bound.
> The Processor properties are set as follows:
> - All 2 processors are checked in Processor Control
> - "Maximum worker threads" is set to 255
> - "Boost SQL Server priority on Windows" is not checked
> - "Use Windows NT fibres" is checked
> - "Use all available processors" is checked
> Why is the CPU not fully utilized? What does "Boost SQL Server priority on
> Windows" do?
> Any assistance would be greatly appreciated
> Thanks

Processor Utilization

Hi
I am running SQL Server 2000 on a 2 processor system (Windows 2003
Standard). I regularily run large queries. When I run the queries the CPU
utilization is consitantly around 11%.
It appears as if something is limiting SQL Server to 11% CPU per
process/query. I have verified that its not I/O bound.
The Processor properties are set as follows:
- All 2 processors are checked in Processor Control
- "Maximum worker threads" is set to 255
- "Boost SQL Server priority on Windows" is not checked
- "Use Windows NT fibres" is checked
- "Use all available processors" is checked
Why is the CPU not fully utilized? What does "Boost SQL Server priority on
Windows" do?
Any assistance would be greatly appreciated
ThanksHi
You may want to check the boost priority option and not use fibres (as
fibres can cause other problems). You could also try using the MAXDOP hint t
o
only use one processor.
Monitor context switches to see if there is an excessive number before
considering using fibres. Ken Hendersons book The Gurus Guide to SQL Server
Architecture and Internals ISBN 0201700476 has a good section on this.
John
"Macisu" wrote:

> Hi
> I am running SQL Server 2000 on a 2 processor system (Windows 2003
> Standard). I regularily run large queries. When I run the queries the CPU
> utilization is consitantly around 11%.
> It appears as if something is limiting SQL Server to 11% CPU per
> process/query. I have verified that its not I/O bound.
> The Processor properties are set as follows:
> - All 2 processors are checked in Processor Control
> - "Maximum worker threads" is set to 255
> - "Boost SQL Server priority on Windows" is not checked
> - "Use Windows NT fibres" is checked
> - "Use all available processors" is checked
> Why is the CPU not fully utilized? What does "Boost SQL Server priority on
> Windows" do?
> Any assistance would be greatly appreciated
> Thanks

Friday, March 30, 2012

Profiler and performance

Profiler and performance

Profiler and alerts

Profiler and alerts

Profiler and alerts

Profiler 2000

Profiler / sql trace

Wednesday, March 28, 2012

Profiler

Profiler

Professional edition vs. SQL Server

Production Report against OLAP database

production or test server...or both

Monday, March 26, 2012

Produciton SQL CPU running around 50%

Produciton SQL CPU running around 50%

Produciton SQL CPU running around 50%

Friday, March 23, 2012

processors replacement

processors replacement

Wednesday, March 21, 2012

Processor Utilization

Processor Utilization

Processor Utilization

Procedure Time out

Blog Archive

About Me