Money Data Type: application

Showing posts with label application. Show all posts

Wednesday, March 28, 2012

More installation woes

We have releases a shrink wrapped winforms application that uses SQL Express as its backend. The product has a fairly large take up, and consequently it is being installed on a large numbre of different machines and configurations. We seem to be having quite a common problem with the installation of SQL Express, we launch the installation from our own installation package with the following command line

SQLEXPR.EXE /qb ADDLOCAL=ALL SQLAUTOSTART=1 INSTANCENAME="OURInstanceName" SQLCOLLATION="Latin1_General_BIN"

We are seeing time after time that the install fails with the message "The SQL Server service failed to start."

Summary.txt points to

SQLSetupNNNN_COMPUTERNAME_SQL.log

and this in turn looks like this

PerfTime Start: Do_sqlScript : Mon Apr 10 14:06:07 2006
Service MSSQL$OURINSTANCENAME with parameters '-m SqlSetup -Q -qLatin1_General_BIN -T4022 -T3659 -T3610 -T4010' is being started at Mon Apr 10 14:06:07 2006
Service failed unexpectedly (1067)
Error Code: 0x8007042b (1067)
Windows Error Text: The process terminated unexpectedly.
Source File Name: sqlsetuplib\service.cpp
Compiler Timestamp: Fri Sep 16 13:20:12 2005
Function Name: sqls::Service::Start
Source Line Number: 301

</snipped>

The ERRORLOG looks something like this

2006-04-10 13:55:47.07 Server Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

2006-04-10 13:55:47.07 Server (c) 2005 Microsoft Corporation.
2006-04-10 13:55:47.07 Server All rights reserved.
2006-04-10 13:55:47.07 Server Server process ID is 1732.
2006-04-10 13:55:47.07 Server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2006-04-10 13:55:47.07 Server Registry startup parameters:
2006-04-10 13:55:47.07 Server -d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2006-04-10 13:55:47.07 Server -e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2006-04-10 13:55:47.07 Server -l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2006-04-10 13:55:47.07 Server Command Line Startup Parameters:
2006-04-10 13:55:47.07 Server -m SqlSetup
2006-04-10 13:55:47.07 Server SqlSetup
2006-04-10 13:55:47.07 Server -Q
2006-04-10 13:55:47.07 Server -q Latin1_General_BIN
2006-04-10 13:55:47.07 Server -T 4022
2006-04-10 13:55:47.07 Server -T 3659
2006-04-10 13:55:47.07 Server -T 3610
2006-04-10 13:55:47.07 Server -T 4010
2006-04-10 13:55:47.08 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2006-04-10 13:55:47.08 Server Detected 2 CPUs. This is an informational message; no user action is required.
2006-04-10 13:55:47.24 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2006-04-10 13:55:47.26 Server Database Mirroring Transport is disabled in the endpoint configuration.
2006-04-10 13:55:47.27 spid5s Warning ******************
2006-04-10 13:55:47.27 spid5s SQL Server started in single-user mode. This an informational message only. No user action is required.
2006-04-10 13:55:47.27 spid5s Starting up database 'master'.
2006-04-10 13:55:47.36 spid5s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2006-04-10 13:55:47.49 spid5s SQL Trace ID 1 was started by login "sa".
2006-04-10 13:55:47.54 spid5s Starting up database 'mssqlsystemresource'.
2006-04-10 13:55:47.76 spid5s Error: 15209, Severity: 16, State: 1.
2006-04-10 13:55:47.76 spid5s An error occurred during encryption.
2006-04-10 13:55:47.76 spid5s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

I can't quite understand what the problem is, has anybody got an idea?

Thanks

We've seen some cases of this. Here's the workaround:

The solution in those cases seemed to be to grant the Network Service FULL CONTROL on C:\Documents and Settings\NetworkService\Application Data\Microsoft\Protect

Hope this helps.

-Jeffrey

|||I am having the same problem installing SQL Server Express.

Here's the error message:
--
Machine : ANSEL
Product : SQL Server Database Services
Error : The SQL Server service failed to start. For more information, see the SQL Server Books Online topics, "How to: View SQL Server 2005 Setup Log Files" and "Starting SQL Server Manually."
--
Machine : ANSEL
Product : Microsoft SQL Server 2005 Express Edition
Product Version : 9.1.2047.00
Install : Failed
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0008_ANSEL_SQL.log
Last Action : InstallFinalize
Error String : The SQL Server service failed to start. For more information, see the SQL Server Books Online topics, "How to: View SQL Server 2005 Setup Log Files" and "Starting SQL Server Manually."
The error is (1067) The process terminated unexpectedly.
Error Number : 29503
--

And the ERRORLOG:

2006-05-11 10:58:59.14 Server Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
Apr 14 2006 01:12:25
Copyright (c) 1988-2005 Microsoft Corporation
Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

2006-05-11 10:58:59.14 Server (c) 2005 Microsoft Corporation.
2006-05-11 10:58:59.14 Server All rights reserved.
2006-05-11 10:58:59.14 Server Server process ID is 736.
2006-05-11 10:58:59.14 Server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2006-05-11 10:58:59.14 Server Registry startup parameters:
2006-05-11 10:58:59.14 Server -d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2006-05-11 10:58:59.14 Server -e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2006-05-11 10:58:59.14 Server -l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2006-05-11 10:58:59.14 Server Command Line Startup Parameters:
2006-05-11 10:58:59.14 Server -m SqlSetup
2006-05-11 10:58:59.14 Server SqlSetup
2006-05-11 10:58:59.14 Server -Q
2006-05-11 10:58:59.14 Server -q SQL_Latin1_General_CP1_CI_AS
2006-05-11 10:58:59.14 Server -T 4022
2006-05-11 10:58:59.14 Server -T 3659
2006-05-11 10:58:59.14 Server -T 3610
2006-05-11 10:58:59.14 Server -T 4010
2006-05-11 10:58:59.31 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2006-05-11 10:58:59.31 Server Detected 1 CPUs. This is an informational message; no user action is required.
2006-05-11 10:59:00.14 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2006-05-11 10:59:00.18 Server Database Mirroring Transport is disabled in the endpoint configuration.
2006-05-11 10:59:00.18 spid5s Warning ******************
2006-05-11 10:59:00.18 spid5s SQL Server started in single-user mode. This an informational message only. No user action is required.
2006-05-11 10:59:00.18 spid5s Starting up database 'master'.
2006-05-11 10:59:00.49 spid5s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2006-05-11 10:59:00.68 spid5s SQL Trace ID 1 was started by login "sa".
2006-05-11 10:59:00.73 spid5s Starting up database 'mssqlsystemresource'.
2006-05-11 10:59:00.74 spid5s The resource database build version is 9.00.2047. This is an informational message only. No user action is required.
2006-05-11 10:59:01.01 spid5s Error: 15209, Severity: 16, State: 1.
2006-05-11 10:59:01.01 spid5s An error occurred during encryption.
2006-05-11 10:59:01.01 spid5s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

I do not have the directory C:\Documents and Settings\NetworkService\Application Data\Microsoft\Protect on my machine. I do have C:\Documents and Settings\[my username]Application Data\Microsoft\Protect so I set the permissions on that as indicated, but it didn't help.

Any other suggestions?

|||Yep, Set Local System to be the Service account for SQL Server. This seems to do the trick.|||

Grant Full rights to "NETWORK SERVICE" on the "C:\Documents and Settings\NetworkService" folder. Let the system take care of the rest.

(Running the "SQL Server (SQLEXPRESS)" service under the "Local System" account also works, but don't do this! The "Local System" account has virtually unlimited access to everything on your computer. "Network Service" has just enough rights to get the job done, without opening a security hole the size of the Grand Canyon.)

|||

Hi,

Sorry if this is a naive question, but what's wrong with using Local System?

We've been having problems with the reliability of the Network Service account when installing SSEE on user's machines. Sometimes the account doesn't exist, or modifying the "C:\Documents and Settings\NetworkService" just isn't enough.

So we had hoped that by using Local System the installation issues will be resolved.

Do you strongly recommend against this idea?

Thanks

Sam

|||Hi Sam_S, the post by T Hunsaker is correct (although perhaps exaggerated a little), Local System does have more privileges than Network Service. However, if you are running SSEE with no network connectivity, and/or using "user instances" to connect to your databases, this will isolate you from most of the potential security threats out there, and yes I have still seen issues with the suggested fix of granting NetworkService full rights on C:\Documents and Settings\NetworkService.|||

Hello Jalperin,

I am having exactly the same problem (also no directory C:\Documents and Settings\NetworkService\Application Data\Microsoft\Protect); did you find an ultimate solution?

Peter

|||

I am having the same issue "C:\Documents and Settings\NetworkService" has the correct permissions (prior to the various install attempts) and I do not have a ".../protect" folder.

...

2006-08-07 15:52:12.71 spid5s Starting up database 'mssqlsystemresource'.
2006-08-07 15:52:12.75 spid5s The resource database build version is 9.00.2047. This is an informational message only. No user action is required.
2006-08-07 15:52:13.41 spid5s Error: 15209, Severity: 16, State: 1.
2006-08-07 15:52:13.41 spid5s An error occurred during encryption.
2006-08-07 15:52:13.49 spid5s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

|||I am having the same issue, and I do not have a network service folder. i am running XP. I have given the network service group full privelages to my local system administrator account. Please advise.

More installation woes

SQLEXPR.EXE /qb ADDLOCAL=ALL SQLAUTOSTART=1 INSTANCENAME="OURInstanceName" SQLCOLLATION="Latin1_General_BIN"

We are seeing time after time that the install fails with the message "The SQL Server service failed to start."

Summary.txt points to

SQLSetupNNNN_COMPUTERNAME_SQL.log

and this in turn looks like this

</snipped>

The ERRORLOG looks something like this

I can't quite understand what the problem is, has anybody got an idea?

Thanks

We've seen some cases of this. Here's the workaround:

The solution in those cases seemed to be to grant the Network Service FULL CONTROL on C:\Documents and Settings\NetworkService\Application Data\Microsoft\Protect

Hope this helps.

-Jeffrey

|||I am having the same problem installing SQL Server Express.

And the ERRORLOG:

Any other suggestions?

|||Yep, Set Local System to be the Service account for SQL Server. This seems to do the trick.|||

Grant Full rights to "NETWORK SERVICE" on the "C:\Documents and Settings\NetworkService" folder. Let the system take care of the rest.

|||

Hi,

Sorry if this is a naive question, but what's wrong with using Local System?

So we had hoped that by using Local System the installation issues will be resolved.

Do you strongly recommend against this idea?

Thanks

Sam

Hello Jalperin,

I am having exactly the same problem (also no directory C:\Documents and Settings\NetworkService\Application Data\Microsoft\Protect); did you find an ultimate solution?

Peter

|||

I am having the same issue "C:\Documents and Settings\NetworkService" has the correct permissions (prior to the various install attempts) and I do not have a ".../protect" folder.

...

More installation woes

SQLEXPR.EXE /qb ADDLOCAL=ALL SQLAUTOSTART=1 INSTANCENAME="OURInstanceName" SQLCOLLATION="Latin1_General_BIN"

We are seeing time after time that the install fails with the message "The SQL Server service failed to start."

Summary.txt points to

SQLSetupNNNN_COMPUTERNAME_SQL.log

and this in turn looks like this

</snipped>

The ERRORLOG looks something like this

I can't quite understand what the problem is, has anybody got an idea?

Thanks

We've seen some cases of this. Here's the workaround:

The solution in those cases seemed to be to grant the Network Service FULL CONTROL on C:\Documents and Settings\NetworkService\Application Data\Microsoft\Protect

Hope this helps.

-Jeffrey

|||I am having the same problem installing SQL Server Express.

And the ERRORLOG:

Any other suggestions?

|||Yep, Set Local System to be the Service account for SQL Server. This seems to do the trick.|||

Grant Full rights to "NETWORK SERVICE" on the "C:\Documents and Settings\NetworkService" folder. Let the system take care of the rest.

|||

Hi,

Sorry if this is a naive question, but what's wrong with using Local System?

So we had hoped that by using Local System the installation issues will be resolved.

Do you strongly recommend against this idea?

Thanks

Sam

Hello Jalperin,

I am having exactly the same problem (also no directory C:\Documents and Settings\NetworkService\Application Data\Microsoft\Protect); did you find an ultimate solution?

Peter

|||

I am having the same issue "C:\Documents and Settings\NetworkService" has the correct permissions (prior to the various install attempts) and I do not have a ".../protect" folder.

...

More installation woes

SQLEXPR.EXE /qb ADDLOCAL=ALL SQLAUTOSTART=1 INSTANCENAME="OURInstanceName" SQLCOLLATION="Latin1_General_BIN"

We are seeing time after time that the install fails with the message "The SQL Server service failed to start."

Summary.txt points to

SQLSetupNNNN_COMPUTERNAME_SQL.log

and this in turn looks like this

</snipped>

The ERRORLOG looks something like this

I can't quite understand what the problem is, has anybody got an idea?

Thanks

We've seen some cases of this. Here's the workaround:

The solution in those cases seemed to be to grant the Network Service FULL CONTROL on C:\Documents and Settings\NetworkService\Application Data\Microsoft\Protect

Hope this helps.

-Jeffrey

|||I am having the same problem installing SQL Server Express.

And the ERRORLOG:

Any other suggestions?

|||Yep, Set Local System to be the Service account for SQL Server. This seems to do the trick.|||

Grant Full rights to "NETWORK SERVICE" on the "C:\Documents and Settings\NetworkService" folder. Let the system take care of the rest.

|||

Hi,

Sorry if this is a naive question, but what's wrong with using Local System?

So we had hoped that by using Local System the installation issues will be resolved.

Do you strongly recommend against this idea?

Thanks

Sam

Hello Jalperin,

I am having exactly the same problem (also no directory C:\Documents and Settings\NetworkService\Application Data\Microsoft\Protect); did you find an ultimate solution?

Peter

|||

I am having the same issue "C:\Documents and Settings\NetworkService" has the correct permissions (prior to the various install attempts) and I do not have a ".../protect" folder.

...

Monday, March 26, 2012

monthtodate and yeartodate

I am creating an application with a report in VB .NET. I have all the data showing up and now I need to create a summary at the bottom of the report to show current, Month To Date, and Year To Date information for formulas and database entries. I have it working to a point. My question is what is the format to write a formula for these two fields to keep the data current even if I am not working in the current month. If I am doing November data it seems to work, but if my date is in October I am not getting the data for October, I am still getting November data. I looked in the Crystal Help file and they suggest to change the date on the computer in the control panel. hmmm.... Not very professional if you ask me. Any help would be great.Look under Report menu in Crystal - you can change the date (last option is "Set Report Print Date/Time").
Change that date and MonthToDate etc will use it for their dependent calculations.
Check the object model under .NET and hopefully that property is exposed and is able to be modified by your .NET code.

dave|||Dave,

Thank you for the reply. I have tried that, but I am still having problems. I can get the current data to show up in the MTD formula but it is not adding it the the previous data for the month. I am sure it is a placement problem but I could be worng there. I am also not getting an accurate sum for a formula that is a division problem. I have ordered a new book to help, but any input you may have would be great. Thank you.

Julie|||Hi Julie,

Here's what I would do:
You know you can create more than one group based on the same report field? Well, you can.
So, say you already have a group to show details date by date, create a group above this based on the same field, but in the "Change Group" options, set the "for each section" option to select 'for each year'.
That will give you a section that will show summary data based on the whole year.

Add another group based on the date, and in the "Change Group" options, set the "for each section" option to select 'for each month'.

The groups may not be in the order you want, so use Report menu, and then Change Group Expert, to define the order:
Should be Group 1 : year
Group 2 : month
Group 3 : day

then create your summaries.

Now in the case of one of my reports, I want data from the current month, even though it's incomplete, so in my report formula for selection by line (Report menu, Edit Selection Formula, Record...)
I use something like this:
(({DATEFIELD} > DateAdd("m", -12, {?Pm-?ReportDate})
and
{DATEFIELD} <= {?Pm-?ReportDate}).
Just bear in mind that if date is 15/12/04, report then goes back to 16/12/03.

IF you want completed months then you need a formula field first, something like this (we'll call it EndDate):

' get first day of current month.
formula = DateValue(Year(CurrentDate), Month(CurrentDate), 1

Then in my record selection formula I'd do this:
(({DATEFIELD} >= DateAdd("m", -12, {@.EndDate})
and
{DATEFIELD} < {@.EndDate}). ' note less than, because must be less than first of current month.........

Hope this helps

dave

Wednesday, March 21, 2012

Monitoring/spying on the CE execution engine

Evening all,

I'm trying to do some profiling of a mobile application to determine where our performance bottleneck is. We have some conflicting information suggesting that inefficient usage of SqlCE might be the cause - but that code exists in a black-box library so we can't see what it's doing.

Are there any tools or configuration options to get the SqlCE execution engine to reveal what connections/queries it's being asked to perform? A simple list with some timestamps would be sufficient - just so we can map from our high-level data...

Any thoughts would be appreciated!
Jack

There is not a Profiler like the one you may use in SQL Server, however you can explore the execution plan that SQL CE's query procesor will use on any given query. Here's how: Connect to your SQL CE database from SQL Server 2005 Management Studio. Open a new query and then from the Query menu choose 'Display Estimated Execution Plan'.

Darren

sql

Monday, March 19, 2012

Monitoring SQL Server Error Message - Severity Level Messages

Hello,
Do SQL server error messages (severilty level messages 11-16 & 17-25),
both 2000 & 2005, get written to the Windows Application Log, as well
as, the SQL server error log? I mainly monitor the server externally
(Windows Event Logs) and would like to NOT have to configure a MAPI
client on SQL to receive the SQL server severity errors.
Thanks,
Dan
The ones that are written to the errorlog are also written to the eventlog.
However, whether an error is to be written to eventlog/errorlog depends on whether it is configured
to do so. In 2000, sysmessages table, check the dlevel column. In 2005, check sys.messages. In 2000,
you can specify whether an error is to be logged (using sp_altermessage), but not in 2005.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"danfoxley@.anon.postalias" <danfoxley@.gmail.com> wrote in message
news:1187251441.579647.169310@.i38g2000prf.googlegr oups.com...
> Hello,
> Do SQL server error messages (severilty level messages 11-16 & 17-25),
> both 2000 & 2005, get written to the Windows Application Log, as well
> as, the SQL server error log? I mainly monitor the server externally
> (Windows Event Logs) and would like to NOT have to configure a MAPI
> client on SQL to receive the SQL server severity errors.
> Thanks,
> Dan
>

Monitoring SQL Server Error Message - Severity Level Messages

Hello,
Do SQL server error messages (severilty level messages 11-16 & 17-25),
both 2000 & 2005, get written to the Windows Application Log, as well
as, the SQL server error log? I mainly monitor the server externally
(Windows Event Logs) and would like to NOT have to configure a MAPI
client on SQL to receive the SQL server severity errors.
Thanks,
DanThe ones that are written to the errorlog are also written to the eventlog.
However, whether an error is to be written to eventlog/errorlog depends on whether it is configured
to do so. In 2000, sysmessages table, check the dlevel column. In 2005, check sys.messages. In 2000,
you can specify whether an error is to be logged (using sp_altermessage), but not in 2005.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"danfoxley@.anon.postalias" <danfoxley@.gmail.com> wrote in message
news:1187251441.579647.169310@.i38g2000prf.googlegroups.com...
> Hello,
> Do SQL server error messages (severilty level messages 11-16 & 17-25),
> both 2000 & 2005, get written to the Windows Application Log, as well
> as, the SQL server error log? I mainly monitor the server externally
> (Windows Event Logs) and would like to NOT have to configure a MAPI
> client on SQL to receive the SQL server severity errors.
> Thanks,
> Dan
>

Monitoring SQL Server Error Message - Severity Level Messages

Hello,
Do SQL server error messages (severilty level messages 11-16 & 17-25),
both 2000 & 2005, get written to the Windows Application Log, as well
as, the SQL server error log? I mainly monitor the server externally
(Windows Event Logs) and would like to NOT have to configure a MAPI
client on SQL to receive the SQL server severity errors.
Thanks,
DanThe ones that are written to the errorlog are also written to the eventlog.
However, whether an error is to be written to eventlog/errorlog depends on w
hether it is configured
to do so. In 2000, sysmessages table, check the dlevel column. In 2005, chec
k sys.messages. In 2000,
you can specify whether an error is to be logged (using sp_altermessage), bu
t not in 2005.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"danfoxley@.anon.postalias" <danfoxley@.gmail.com> wrote in message
news:1187251441.579647.169310@.i38g2000prf.googlegroups.com...
> Hello,
> Do SQL server error messages (severilty level messages 11-16 & 17-25),
> both 2000 & 2005, get written to the Windows Application Log, as well
> as, the SQL server error log? I mainly monitor the server externally
> (Windows Event Logs) and would like to NOT have to configure a MAPI
> client on SQL to receive the SQL server severity errors.
> Thanks,
> Dan
>

Monitoring Software

Hello,
Our SQL Application is supported by SQL servers and non-SQL servers. We
have software to monitor all the SQL servers remotely (SQLDiagnostics
by Idera) and SQL changes to both SQL and the server it runs on,
(SQLconfig by Idera).
I have a need to do remote monitoring of the non-SQL servers form
performance and any changes to the server (new users, patches, addition
of new software, removal of software and so forth).
I know this is a bit off topic, but I hope someone here has simular
needs to monitor their entire SQL enterprise.

Does anyone know of an enterprise solution to monitor and track changes
to Windows 2003 servers?
TIA
RobOperations Manager?
http://www.microsoft.com/mom/default.mspx

--
David Portas
SQL Server MVP
--

Monitoring service broker queues through a .NET process

Is there a way for a .NET application to receive a notification when a service broker queue has been updated with a new message? I tried using SqlDependency on an SB queue but I got an "invalid" error in my notification handler.

Such a notification would be much better than having to poll the queue every N seconds.

Thanks

Issue a WAITFOR(RECEIVE.. ) on the queue with no timeout? Make sure you set the CommandTimeout on the SqlCommand to infinite.|||I will try that. Thanks very much

Monitoring Query Performance

All,
I have a question regarding query performance. I have a SQL 2000 server that
is acting as a backend for a business reporting application (IB WebFocus).
Most of the time, the system runs along at a fairly descent clip, but there
are times when our users (who might be new to WebFocus) submit queries that
hang up the system. I will notice SQLSERVR.EXE is jammed up at 100% CPU
usage, and I end up hving to kill some sessions (in the reporting app) before
CPU usage is back under control.
I would like to try and be more proactive than that... what are some ways I
can identify the queries that are running when this happens? Is setting up a
trace file my best bet, or...?
Thanks in advance...
David
monitor the sysprocess system table. You can also use sp_who or sp_who2 to
see the contents of this table.
look for processes that have gone awry...or are blocking other users. Use
net send to notify the user...then Use kill <spid> to kick them.
"David Levine" <DavidLevine@.discussions.microsoft.com> wrote in message
news:953DFEF8-18E6-4BA1-AD4B-436310DE5E09@.microsoft.com...
> All,
> I have a question regarding query performance. I have a SQL 2000 server
> that
> is acting as a backend for a business reporting application (IB WebFocus).
> Most of the time, the system runs along at a fairly descent clip, but
> there
> are times when our users (who might be new to WebFocus) submit queries
> that
> hang up the system. I will notice SQLSERVR.EXE is jammed up at 100% CPU
> usage, and I end up hving to kill some sessions (in the reporting app)
> before
> CPU usage is back under control.
> I would like to try and be more proactive than that... what are some ways
> I
> can identify the queries that are running when this happens? Is setting up
> a
> trace file my best bet, or...?
> Thanks in advance...
> David
|||Yes...pretty much. I had the same issue a couple of years
ago at a client site using WebFocus. It always turned out to
be the ad hoc queries that the users ran through the tool -
and some of the ugly ways the query was built by the tool
before sending it to SQL. You'll need to trace to find the
issues.
-Sue
On Tue, 24 Jan 2006 15:26:13 -0800, David Levine
<DavidLevine@.discussions.microsoft.com> wrote:

>All,
>I have a question regarding query performance. I have a SQL 2000 server that
>is acting as a backend for a business reporting application (IB WebFocus).
>Most of the time, the system runs along at a fairly descent clip, but there
>are times when our users (who might be new to WebFocus) submit queries that
>hang up the system. I will notice SQLSERVR.EXE is jammed up at 100% CPU
>usage, and I end up hving to kill some sessions (in the reporting app) before
>CPU usage is back under control.
>I would like to try and be more proactive than that... what are some ways I
>can identify the queries that are running when this happens? Is setting up a
>trace file my best bet, or...?
>Thanks in advance...
>David

Monday, March 12, 2012

Monitoring Query Performance

All,
I have a question regarding query performance. I have a SQL 2000 server that
is acting as a backend for a business reporting application (IB WebFocus).
Most of the time, the system runs along at a fairly descent clip, but there
are times when our users (who might be new to WebFocus) submit queries that
hang up the system. I will notice SQLSERVR.EXE is jammed up at 100% CPU
usage, and I end up hving to kill some sessions (in the reporting app) before
CPU usage is back under control.
I would like to try and be more proactive than that... what are some ways I
can identify the queries that are running when this happens? Is setting up a
trace file my best bet, or...?
Thanks in advance...
Davidmonitor the sysprocess system table. You can also use sp_who or sp_who2 to
see the contents of this table.
look for processes that have gone awry...or are blocking other users. Use
net send to notify the user...then Use kill <spid> to kick them.
"David Levine" <DavidLevine@.discussions.microsoft.com> wrote in message
news:953DFEF8-18E6-4BA1-AD4B-436310DE5E09@.microsoft.com...
> All,
> I have a question regarding query performance. I have a SQL 2000 server
> that
> is acting as a backend for a business reporting application (IB WebFocus).
> Most of the time, the system runs along at a fairly descent clip, but
> there
> are times when our users (who might be new to WebFocus) submit queries
> that
> hang up the system. I will notice SQLSERVR.EXE is jammed up at 100% CPU
> usage, and I end up hving to kill some sessions (in the reporting app)
> before
> CPU usage is back under control.
> I would like to try and be more proactive than that... what are some ways
> I
> can identify the queries that are running when this happens? Is setting up
> a
> trace file my best bet, or...?
> Thanks in advance...
> David|||Yes...pretty much. I had the same issue a couple of years
ago at a client site using WebFocus. It always turned out to
be the ad hoc queries that the users ran through the tool -
and some of the ugly ways the query was built by the tool
before sending it to SQL. You'll need to trace to find the
issues.
-Sue
On Tue, 24 Jan 2006 15:26:13 -0800, David Levine
<DavidLevine@.discussions.microsoft.com> wrote:
>All,
>I have a question regarding query performance. I have a SQL 2000 server that
>is acting as a backend for a business reporting application (IB WebFocus).
>Most of the time, the system runs along at a fairly descent clip, but there
>are times when our users (who might be new to WebFocus) submit queries that
>hang up the system. I will notice SQLSERVR.EXE is jammed up at 100% CPU
>usage, and I end up hving to kill some sessions (in the reporting app) before
>CPU usage is back under control.
>I would like to try and be more proactive than that... what are some ways I
>can identify the queries that are running when this happens? Is setting up a
>trace file my best bet, or...?
>Thanks in advance...
>David

Monitoring Query Performance

All,
I have a question regarding query performance. I have a SQL 2000 server that
is acting as a backend for a business reporting application (IB WebFocus).
Most of the time, the system runs along at a fairly descent clip, but there
are times when our users (who might be new to WebFocus) submit queries that
hang up the system. I will notice SQLSERVR.EXE is jammed up at 100% CPU
usage, and I end up hving to kill some sessions (in the reporting app) befor
e
CPU usage is back under control.
I would like to try and be more proactive than that... what are some ways I
can identify the queries that are running when this happens? Is setting up a
trace file my best bet, or...?
Thanks in advance...
Davidmonitor the sysprocess system table. You can also use sp_who or sp_who2 to
see the contents of this table.
look for processes that have gone awry...or are blocking other users. Use
net send to notify the user...then Use kill <spid> to kick them.
"David Levine" <DavidLevine@.discussions.microsoft.com> wrote in message
news:953DFEF8-18E6-4BA1-AD4B-436310DE5E09@.microsoft.com...
> All,
> I have a question regarding query performance. I have a SQL 2000 server
> that
> is acting as a backend for a business reporting application (IB WebFocus).
> Most of the time, the system runs along at a fairly descent clip, but
> there
> are times when our users (who might be new to WebFocus) submit queries
> that
> hang up the system. I will notice SQLSERVR.EXE is jammed up at 100% CPU
> usage, and I end up hving to kill some sessions (in the reporting app)
> before
> CPU usage is back under control.
> I would like to try and be more proactive than that... what are some ways
> I
> can identify the queries that are running when this happens? Is setting up
> a
> trace file my best bet, or...?
> Thanks in advance...
> David|||Yes...pretty much. I had the same issue a couple of years
ago at a client site using WebFocus. It always turned out to
be the ad hoc queries that the users ran through the tool -
and some of the ugly ways the query was built by the tool
before sending it to SQL. You'll need to trace to find the
issues.
-Sue
On Tue, 24 Jan 2006 15:26:13 -0800, David Levine
<DavidLevine@.discussions.microsoft.com> wrote:

>All,
>I have a question regarding query performance. I have a SQL 2000 server tha
t
>is acting as a backend for a business reporting application (IB WebFocus).
>Most of the time, the system runs along at a fairly descent clip, but there
>are times when our users (who might be new to WebFocus) submit queries that
>hang up the system. I will notice SQLSERVR.EXE is jammed up at 100% CPU
>usage, and I end up hving to kill some sessions (in the reporting app) befo
re
>CPU usage is back under control.
>I would like to try and be more proactive than that... what are some ways
I
>can identify the queries that are running when this happens? Is setting up
a
>trace file my best bet, or...?
>Thanks in advance...
>David

Monitoring Database hits

In any asp.net application, whats the simplest way to monitor how many times a page hits the database (opens and closes a connection)?

JontyMC:
In any asp.net application, whats the simplest way to monitor how many times a page hits the database (opens and closes a connection)?

If you are using a data access layer, you can write some code to log when the data is connection/executed. You can log this to a text file, or a database, or any other external data source.

Friday, March 9, 2012

Monitoring changes in any table in an instance

Is there a way to monitor changes in any tables of an instance? I have a
database driven application that I want to reverse engineer. I want to find
out if a function would perform changes to which tables in the instance. Is
there a realistic way to do this? Thank you.
Lito Kusnadi
Lito
CREATE TABLE AuditDDLEvents
(
LSN INT NOT NULL IDENTITY,
posttime DATETIME NOT NULL,
eventtype SYSNAME NOT NULL,
loginname SYSNAME NOT NULL,
schemaname SYSNAME NOT NULL,
objectname SYSNAME NOT NULL,
targetobjectname SYSNAME NOT NULL,
eventdata XML NOT NULL,
CONSTRAINT PK_AuditDDLEvents PRIMARY KEY(LSN)
)
GO
CREATE TRIGGER trg_audit_ddl_events ON DATABASE FOR
DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @.eventdata AS XML
SET @.eventdata = eventdata()
INSERT INTO dbo.AuditDDLEvents(
posttime, eventtype, loginname, schemaname,
objectname, targetobjectname, eventdata)
VALUES(
CAST(@.eventdata.query('data(//PostTime)') AS VARCHAR(23)),
CAST(@.eventdata.query('data(//EventType)') AS SYSNAME),
CAST(@.eventdata.query('data(//LoginName)') AS SYSNAME),
CAST(@.eventdata.query('data(//SchemaName)') AS SYSNAME),
CAST(@.eventdata.query('data(//ObjectName)') AS SYSNAME),
CAST(@.eventdata.query('data(//TargetObjectName)') AS SYSNAME),
@.eventdata)
GO
The trigger simply extracts all event attributes
of interest from the eventdata() function using XQuery,
and inserts those into the AuditDDLEvents table. To test the trigger,
submit a few DDL statements and query the audit table:
CREATE TABLE T1(col1 INT NOT NULL PRIMARY KEY)
ALTER TABLE T1 ADD col2 INT NULL
ALTER TABLE T1 ALTER COLUMN col2 INT NOT NULL
CREATE NONCLUSTERED INDEX idx1 ON T1(col2)
SELECT * FROM AuditDDLEvents
SELECT posttime, eventtype, loginname,
CAST(eventdata.query('data(//TSQLCommand)') AS NVARCHAR(2000))
AS tsqlcommand
FROM dbo.AuditDDLEvents
WHERE schemaname = N'dbo' AND N'T1' IN(objectname, targetobjectname)
ORDER BY posttime
"Lito Kusnadi" <LitoKusnadi@.discussions.microsoft.com> wrote in message
news:E78250B3-C634-4ADA-9262-24C85C4EFC92@.microsoft.com...
> Is there a way to monitor changes in any tables of an instance? I have a
> database driven application that I want to reverse engineer. I want to
> find
> out if a function would perform changes to which tables in the instance.
> Is
> there a realistic way to do this? Thank you.
> --
> Lito Kusnadi
>
|||In addition to Uri's recommendation you may want to take a look at the
information available in SQL Server 2005 default trace.
Take a look at this report in Management Studio, Reports - Standard Reports
- Schema Changes History.
Also see the path of the trace file by running
select * from sys.traces
where the default trace is usually id 1.
Hope this helps,
Ben Nevarez
"Uri Dimant" wrote:

> Lito
> CREATE TABLE AuditDDLEvents
> (
> LSN INT NOT NULL IDENTITY,
> posttime DATETIME NOT NULL,
> eventtype SYSNAME NOT NULL,
> loginname SYSNAME NOT NULL,
> schemaname SYSNAME NOT NULL,
> objectname SYSNAME NOT NULL,
> targetobjectname SYSNAME NOT NULL,
> eventdata XML NOT NULL,
> CONSTRAINT PK_AuditDDLEvents PRIMARY KEY(LSN)
> )
> GO
> CREATE TRIGGER trg_audit_ddl_events ON DATABASE FOR
> DDL_DATABASE_LEVEL_EVENTS
> AS
> DECLARE @.eventdata AS XML
> SET @.eventdata = eventdata()
> INSERT INTO dbo.AuditDDLEvents(
> posttime, eventtype, loginname, schemaname,
> objectname, targetobjectname, eventdata)
> VALUES(
> CAST(@.eventdata.query('data(//PostTime)') AS VARCHAR(23)),
> CAST(@.eventdata.query('data(//EventType)') AS SYSNAME),
> CAST(@.eventdata.query('data(//LoginName)') AS SYSNAME),
> CAST(@.eventdata.query('data(//SchemaName)') AS SYSNAME),
> CAST(@.eventdata.query('data(//ObjectName)') AS SYSNAME),
> CAST(@.eventdata.query('data(//TargetObjectName)') AS SYSNAME),
> @.eventdata)
> GO
> The trigger simply extracts all event attributes
> of interest from the eventdata() function using XQuery,
> and inserts those into the AuditDDLEvents table. To test the trigger,
> submit a few DDL statements and query the audit table:
>
> CREATE TABLE T1(col1 INT NOT NULL PRIMARY KEY)
> ALTER TABLE T1 ADD col2 INT NULL
> ALTER TABLE T1 ALTER COLUMN col2 INT NOT NULL
> CREATE NONCLUSTERED INDEX idx1 ON T1(col2)
> SELECT * FROM AuditDDLEvents
> SELECT posttime, eventtype, loginname,
> CAST(eventdata.query('data(//TSQLCommand)') AS NVARCHAR(2000))
> AS tsqlcommand
> FROM dbo.AuditDDLEvents
> WHERE schemaname = N'dbo' AND N'T1' IN(objectname, targetobjectname)
> ORDER BY posttime
>
> "Lito Kusnadi" <LitoKusnadi@.discussions.microsoft.com> wrote in message
> news:E78250B3-C634-4ADA-9262-24C85C4EFC92@.microsoft.com...
>
>

Monitoring changes in any table in an instance

Is there a way to monitor changes in any tables of an instance? I have a
database driven application that I want to reverse engineer. I want to find
out if a function would perform changes to which tables in the instance. Is
there a realistic way to do this? Thank you.
--
Lito KusnadiLito
CREATE TABLE AuditDDLEvents
(
LSN INT NOT NULL IDENTITY,
posttime DATETIME NOT NULL,
eventtype SYSNAME NOT NULL,
loginname SYSNAME NOT NULL,
schemaname SYSNAME NOT NULL,
objectname SYSNAME NOT NULL,
targetobjectname SYSNAME NOT NULL,
eventdata XML NOT NULL,
CONSTRAINT PK_AuditDDLEvents PRIMARY KEY(LSN)
)
GO
CREATE TRIGGER trg_audit_ddl_events ON DATABASE FOR
DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @.eventdata AS XML
SET @.eventdata = eventdata()
INSERT INTO dbo.AuditDDLEvents(
posttime, eventtype, loginname, schemaname,
objectname, targetobjectname, eventdata)
VALUES(
CAST(@.eventdata.query('data(//PostTime)') AS VARCHAR(23)),
CAST(@.eventdata.query('data(//EventType)') AS SYSNAME),
CAST(@.eventdata.query('data(//LoginName)') AS SYSNAME),
CAST(@.eventdata.query('data(//SchemaName)') AS SYSNAME),
CAST(@.eventdata.query('data(//ObjectName)') AS SYSNAME),
CAST(@.eventdata.query('data(//TargetObjectName)') AS SYSNAME),
@.eventdata)
GO
The trigger simply extracts all event attributes
of interest from the eventdata() function using XQuery,
and inserts those into the AuditDDLEvents table. To test the trigger,
submit a few DDL statements and query the audit table:
CREATE TABLE T1(col1 INT NOT NULL PRIMARY KEY)
ALTER TABLE T1 ADD col2 INT NULL
ALTER TABLE T1 ALTER COLUMN col2 INT NOT NULL
CREATE NONCLUSTERED INDEX idx1 ON T1(col2)
SELECT * FROM AuditDDLEvents
SELECT posttime, eventtype, loginname,
CAST(eventdata.query('data(//TSQLCommand)') AS NVARCHAR(2000))
AS tsqlcommand
FROM dbo.AuditDDLEvents
WHERE schemaname = N'dbo' AND N'T1' IN(objectname, targetobjectname)
ORDER BY posttime
"Lito Kusnadi" <LitoKusnadi@.discussions.microsoft.com> wrote in message
news:E78250B3-C634-4ADA-9262-24C85C4EFC92@.microsoft.com...
> Is there a way to monitor changes in any tables of an instance? I have a
> database driven application that I want to reverse engineer. I want to
> find
> out if a function would perform changes to which tables in the instance.
> Is
> there a realistic way to do this? Thank you.
> --
> Lito Kusnadi
>|||In addition to Uri's recommendation you may want to take a look at the
information available in SQL Server 2005 default trace.
Take a look at this report in Management Studio, Reports - Standard Reports
- Schema Changes History.
Also see the path of the trace file by running
select * from sys.traces
where the default trace is usually id 1.
Hope this helps,
Ben Nevarez
"Uri Dimant" wrote:
> Lito
> CREATE TABLE AuditDDLEvents
> (
> LSN INT NOT NULL IDENTITY,
> posttime DATETIME NOT NULL,
> eventtype SYSNAME NOT NULL,
> loginname SYSNAME NOT NULL,
> schemaname SYSNAME NOT NULL,
> objectname SYSNAME NOT NULL,
> targetobjectname SYSNAME NOT NULL,
> eventdata XML NOT NULL,
> CONSTRAINT PK_AuditDDLEvents PRIMARY KEY(LSN)
> )
> GO
> CREATE TRIGGER trg_audit_ddl_events ON DATABASE FOR
> DDL_DATABASE_LEVEL_EVENTS
> AS
> DECLARE @.eventdata AS XML
> SET @.eventdata = eventdata()
> INSERT INTO dbo.AuditDDLEvents(
> posttime, eventtype, loginname, schemaname,
> objectname, targetobjectname, eventdata)
> VALUES(
> CAST(@.eventdata.query('data(//PostTime)') AS VARCHAR(23)),
> CAST(@.eventdata.query('data(//EventType)') AS SYSNAME),
> CAST(@.eventdata.query('data(//LoginName)') AS SYSNAME),
> CAST(@.eventdata.query('data(//SchemaName)') AS SYSNAME),
> CAST(@.eventdata.query('data(//ObjectName)') AS SYSNAME),
> CAST(@.eventdata.query('data(//TargetObjectName)') AS SYSNAME),
> @.eventdata)
> GO
> The trigger simply extracts all event attributes
> of interest from the eventdata() function using XQuery,
> and inserts those into the AuditDDLEvents table. To test the trigger,
> submit a few DDL statements and query the audit table:
>
> CREATE TABLE T1(col1 INT NOT NULL PRIMARY KEY)
> ALTER TABLE T1 ADD col2 INT NULL
> ALTER TABLE T1 ALTER COLUMN col2 INT NOT NULL
> CREATE NONCLUSTERED INDEX idx1 ON T1(col2)
> SELECT * FROM AuditDDLEvents
> SELECT posttime, eventtype, loginname,
> CAST(eventdata.query('data(//TSQLCommand)') AS NVARCHAR(2000))
> AS tsqlcommand
> FROM dbo.AuditDDLEvents
> WHERE schemaname = N'dbo' AND N'T1' IN(objectname, targetobjectname)
> ORDER BY posttime
>
> "Lito Kusnadi" <LitoKusnadi@.discussions.microsoft.com> wrote in message
> news:E78250B3-C634-4ADA-9262-24C85C4EFC92@.microsoft.com...
> > Is there a way to monitor changes in any tables of an instance? I have a
> > database driven application that I want to reverse engineer. I want to
> > find
> > out if a function would perform changes to which tables in the instance.
> > Is
> > there a realistic way to do this? Thank you.
> >
> > --
> > Lito Kusnadi
> >
>
>

monitoring application performence

hi all
I'm running a sql 2000 standard edition server on win 2000 server.
one instance on a 3 gb memory, 2 cpus ,machine.
there are about 20 databases on the server; however I'm encounering a
problem with a spacific 500 mb db.
the problem is: 7 client stations located at a different city communicating
(only with)the problematic db on the server threw a single application via
iis. for the last few months the respons time is queit slow. tables are
reletivly small (the biggest is 60000 rows but the rest a only a few hunderd
each)
now... I'm trying yo isolate the problem.
my first question: is it true to say that since the rest of the applications
that are running against the other dbs are running smoothly the problem is
not at the server lelvel or at the network level?
also, opening the task mamager on the server machine I see that the cpu isnt
realy busy and that there is no paging.
I captured the a application queries with the profiler and I saw that the
cpu/ read/writes times are low.
there is no fragmentation on the indexes.
I also run the index tunning wizard with the profiler file. it indicated a
44% improvment and suggested 5 new indexes; but I still belive the problem
lays somewhere else.
and, the system guy, who is monitoring the network traffic, tells me it's
not the network as the bendwidth is 50% free.
so... where should I aim at next?
thanks,
alon.If the rest of the databases are doing fine except this one, then yes, the
problem is at the database level not at the server level.
You said, Index Tuning Wizard suggested new indexes, and the predicted
performance gain is substantial. I think you should go for it, after
testing.
To start with identify which stored procedures are running slow. Use
Profiler to identify the long running stored procedures (look at the
duration column, time is in milliseconds). once you identified the stored
procedures, try running them individually from Query Analyzer, with
'graphical execution plan' turned on. If you see index or table scans, then
the query is not written well, or there are no supporting indexes for that
query. Then try applying the indexes suggested by Index Tuning Wizard and
see if your query plan and response times improve.
Again, simply keep adding indexes is not really good for an OLTP system. if
you are trying to run report-like queries on an OLTP system, then you might
want to replicate the data to a different database, and run your queries
there. You could use transactional replication for this purpose. With
transactional replication the subscriber is only a few seconds behind the
publisher, in general.
Using Profiler, if you do find that the stored procedures and queries are
indeed running fine, the bottle neck is either the network or the
application.
Also check that there's no blockig going on in your database. You could use
sp_who or sp_who2 to see if any of the spids are getting blocked. Blocked
spid have a non-zero value in the 'blocked' column.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"alon" <alon@.discussions.microsoft.com> wrote in message
news:6467A9F4-A2C8-4EB8-9C2D-60286D564E6C@.microsoft.com...
> hi all
> I'm running a sql 2000 standard edition server on win 2000 server.
> one instance on a 3 gb memory, 2 cpus ,machine.
> there are about 20 databases on the server; however I'm encounering a
> problem with a spacific 500 mb db.
> the problem is: 7 client stations located at a different city
> communicating
> (only with)the problematic db on the server threw a single application
> via
> iis. for the last few months the respons time is queit slow. tables are
> reletivly small (the biggest is 60000 rows but the rest a only a few
> hunderd
> each)
> now... I'm trying yo isolate the problem.
> my first question: is it true to say that since the rest of the
> applications
> that are running against the other dbs are running smoothly the problem is
> not at the server lelvel or at the network level?
> also, opening the task mamager on the server machine I see that the cpu
> isnt
> realy busy and that there is no paging.
> I captured the a application queries with the profiler and I saw that the
> cpu/ read/writes times are low.
> there is no fragmentation on the indexes.
> I also run the index tunning wizard with the profiler file. it indicated a
> 44% improvment and suggested 5 new indexes; but I still belive the problem
> lays somewhere else.
> and, the system guy, who is monitoring the network traffic, tells me it's
> not the network as the bendwidth is 50% free.
> so... where should I aim at next?
> thanks,
> alon.
>

monitoring application performence

hi all
I'm running a sql 2000 standard edition server on win 2000 server.
one instance on a 3 gb memory, 2 cpus ,machine.
there are about 20 databases on the server; however I'm encounering a
problem with a spacific 500 mb db.
the problem is: 7 client stations located at a different city communicating
(only with)the problematic db on the server threw a single application via
iis. for the last few months the respons time is queit slow. tables are
reletivly small (the biggest is 60000 rows but the rest a only a few hunderd
each)
now... I'm trying yo isolate the problem.
my first question: is it true to say that since the rest of the applications
that are running against the other dbs are running smoothly the problem is
not at the server lelvel or at the network level?
also, opening the task mamager on the server machine I see that the cpu isnt
realy busy and that there is no paging.
I captured the a application queries with the profiler and I saw that the
cpu/ read/writes times are low.
there is no fragmentation on the indexes.
I also run the index tunning wizard with the profiler file. it indicated a
44% improvment and suggested 5 new indexes; but I still belive the problem
lays somewhere else.
and, the system guy, who is monitoring the network traffic, tells me it's
not the network as the bendwidth is 50% free.
so... where should I aim at next?
thanks,
alon.
If the rest of the databases are doing fine except this one, then yes, the
problem is at the database level not at the server level.
You said, Index Tuning Wizard suggested new indexes, and the predicted
performance gain is substantial. I think you should go for it, after
testing.
To start with identify which stored procedures are running slow. Use
Profiler to identify the long running stored procedures (look at the
duration column, time is in milliseconds). once you identified the stored
procedures, try running them individually from Query Analyzer, with
'graphical execution plan' turned on. If you see index or table scans, then
the query is not written well, or there are no supporting indexes for that
query. Then try applying the indexes suggested by Index Tuning Wizard and
see if your query plan and response times improve.
Again, simply keep adding indexes is not really good for an OLTP system. if
you are trying to run report-like queries on an OLTP system, then you might
want to replicate the data to a different database, and run your queries
there. You could use transactional replication for this purpose. With
transactional replication the subscriber is only a few seconds behind the
publisher, in general.
Using Profiler, if you do find that the stored procedures and queries are
indeed running fine, the bottle neck is either the network or the
application.
Also check that there's no blockig going on in your database. You could use
sp_who or sp_who2 to see if any of the spids are getting blocked. Blocked
spid have a non-zero value in the 'blocked' column.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"alon" <alon@.discussions.microsoft.com> wrote in message
news:6467A9F4-A2C8-4EB8-9C2D-60286D564E6C@.microsoft.com...
> hi all
> I'm running a sql 2000 standard edition server on win 2000 server.
> one instance on a 3 gb memory, 2 cpus ,machine.
> there are about 20 databases on the server; however I'm encounering a
> problem with a spacific 500 mb db.
> the problem is: 7 client stations located at a different city
> communicating
> (only with)the problematic db on the server threw a single application
> via
> iis. for the last few months the respons time is queit slow. tables are
> reletivly small (the biggest is 60000 rows but the rest a only a few
> hunderd
> each)
> now... I'm trying yo isolate the problem.
> my first question: is it true to say that since the rest of the
> applications
> that are running against the other dbs are running smoothly the problem is
> not at the server lelvel or at the network level?
> also, opening the task mamager on the server machine I see that the cpu
> isnt
> realy busy and that there is no paging.
> I captured the a application queries with the profiler and I saw that the
> cpu/ read/writes times are low.
> there is no fragmentation on the indexes.
> I also run the index tunning wizard with the profiler file. it indicated a
> 44% improvment and suggested 5 new indexes; but I still belive the problem
> lays somewhere else.
> and, the system guy, who is monitoring the network traffic, tells me it's
> not the network as the bendwidth is 50% free.
> so... where should I aim at next?
> thanks,
> alon.
>

Wednesday, March 28, 2012

More installation woes

More installation woes

More installation woes

More installation woes

Monday, March 26, 2012

monthtodate and yeartodate

Wednesday, March 21, 2012

Monitoring/spying on the CE execution engine

Monday, March 19, 2012

Monitoring SQL Server Error Message - Severity Level Messages

Monitoring SQL Server Error Message - Severity Level Messages

Monitoring SQL Server Error Message - Severity Level Messages

Monitoring Software

Monitoring service broker queues through a .NET process

Monitoring Query Performance

Monday, March 12, 2012

Monitoring Query Performance

Monitoring Query Performance

Monitoring Database hits

Friday, March 9, 2012

Monitoring changes in any table in an instance

Monitoring changes in any table in an instance

monitoring application performence

monitoring application performence

monitoring application performence

Money Data Type

Blog Archive

About Me