?
Solved

What's the cause of my frequent 50% CPU usage in 1 of 3 instances of SQL 2000

Posted on 2007-10-03
48
Medium Priority
?
1,282 Views
Last Modified: 2013-12-05
Hi,
Every so often one of my instances of SQL Server 2000 frequently keeps using 45-50% CPU (Every 2-3 secs.)
After a reboot things go back to normal for a period of weeks of months, then starts again.

Windows 2000 Server SP4 (DHCP,DNS, SQL,File, Print)
SQL Server 2000 SP4 x 2 instance , MSDE x 1
XEON 2.4
4 GB RAM (Avg MB available = 775MB)
6 SCSI HDDs (RAID 5)

As far as I can tell from Performance Monitor, the machine is handling the workload easily.
SQL Profiler also doesn't report any reason for the frequent peaks. (That I can tell)
SEM doesn't show any significant locking. (As far as I know)

In the past I have been able to link this problem to NTDBSMGE.EXE from Pervasive.
Pervasive is still installed on the machine, but has no activity and no CPU usage.

Has any one got any idea's about this?
How can I tell which instance is the problem instance and how can I find and resolve the issue?
0
Comment
Question by:DennisPost
  • 27
  • 11
  • 4
  • +2
48 Comments
 
LVL 8

Assisted Solution

by:MrRobot
MrRobot earned 1000 total points
ID: 20005984
you can use performance monitor to monitor some sql server counters for any instance you want. you can check for full scans, disk usage, and others.

also, you can check activity monitor from management studio, which gives you cpu usage for the active processes.
0
 
LVL 2

Author Comment

by:DennisPost
ID: 20006265
Hi MrRobot,

Thanks for your comment.

I know the theory behind Performace monitor, just have little experience with the practical.
For starters, how do I know which sqlserver.exe goes with which instance of sql server.
If I look at the counters under Process I see sqlserver#1, sqlserver#2, sqlserver#3

Once I have that I could look at Process / %CPU for the instance, but that will only confirm what I can see in task manager i.e. it uses CPU in fits and bouts.

In short, I need instructions on how to find out which instance of SQL is giving me the trouble and how
I can find out what is causing the issue. Then I might need help resolving the issue.
0
 
LVL 8

Assisted Solution

by:MrRobot
MrRobot earned 1000 total points
ID: 20006808
Hi Dennis,

open the task manager with the PIDs being shown.
go to a command prompt and type "tasklist /svc". from the list, you can find out which PID is attached to which instance.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 21

Assisted Solution

by:mastoo
mastoo earned 200 total points
ID: 20007008
MrRobot's tip will identify the instance.  If you then want to identify the operation within that instance and can catch it while it is happening use the sqlhogs script in this:

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_22418533.html
0
 
LVL 2

Author Comment

by:DennisPost
ID: 20032797
Hi,

Is tasklist /svc also fpr Windows 2000 Server?
It isn't recognised.
How else can I find out which one is the naughty instance?

Mastoo,
I ran the script against each the Master db of the instances. One (MSDE) didn't return any results.
The other 2 returned  one line with CPUPercent = 0.
I ran your script when CPU% was >50.

I also tried the old version, again it is only seeing the execution in QA and nothing more. Maybe I am in the wrong instance???

Cheers

0
 
LVL 21

Assisted Solution

by:mastoo
mastoo earned 200 total points
ID: 20034287
If you see an instance of sql server in task mgr using cpu and the script gave you nothing back, I think that would mean the wrong instance.  You can't just run it against all instances?  Without tasklist you could see if sc is available to correlate the instance with a pid.  I think "sc queryex" would show you services and their pid's.
0
 
LVL 9

Assisted Solution

by:dreamyguy
dreamyguy earned 200 total points
ID: 20050053
Here's what you can do to find out the cause of the frequent CPU usage.

Download process explorer from www.sysinternals.com

run process explorer and doubleclick on that instance of sql server which is consuming most of the cpu.
this will show you the thread related information.
now double click on the thread(s) which is consuming most of the cpu.
this will show you the stack of that particular thread.
by looking at the stack, you can determine the modules and the functions being called which cause the high cpu utilization.
you could then do an online search to see if there are any updates for that particular module/component.
0
 
LVL 9

Assisted Solution

by:dreamyguy
dreamyguy earned 200 total points
ID: 20050088
In order to find out the same using perfmon, you can do the following:

1. Go to Start > Programs > Administrative Tools > Performance.
2. In the Performance Window under Performance Logs and Alerts option right click on Counter Logs option
3. Click on New Log settings option and assign a name to the New Log.
4. Click on Ok button and in the next window click on Add Counters window
5. From the Performance Object select the following options:
             Process
             Processor
             Thread
6. Please make sure that all counters and All Instances option are selected in the same window.
7. Click OK and set the sample interval to 5 seconds.
8. Then select the tab Log Files in the main window. In the logfile type select as Binary Circular File and click on Configure Button to specify the location of the Log file. Also you can specify the limit of the log file to 200 MB in the same window. Click OK again and in the main window select the tab Schedule the option Start log and Stop Log should be selected as Manually. Click on Apply and OK to exit from the main Window.
9. In the right pane of Perfmon Window you will see the new log file which has been created. Right click on that and click on Start option. Please allow Perfmon to run till the issue occurs.
10. Once it is observed that the issue has started to occur, lets leave Perfmon running for 10 minutes so that it can gather snapshots of all the selected counter objects.
11. After 10 minutes, please right click on the log file name and click on stop and upload it to any FTP site for analysis across after compressing it and give us the link so that we can analyse it.
0
 
LVL 2

Author Comment

by:DennisPost
ID: 20055309
Hi, just got back in the office.....

Mastoo,
I did run the script against each of the instances.
sc queryex is not recognized.

Dreamguy,
I downloaded Process Explorer and now Microsoft Debugging Tools For Windows for the DbgHelp.dll.
It tells me it is needed when I click on the threads tab.
On the services tab I can see which instance I am looking at.
Looks like I might try some of those other tools as well soon. Thanks for the tip!

I have also setup the performance couter logs as per your specification. Alas, I do not have access to any ftp site. Could you maybe recommend any?


BUT....... Automatic Updates updated my server last night and rebooted. Now the problem has gone and I have no strange issues to monitor anymore. Users happy, me disgruntled.
I'm sure this problem will popup again in future as it seems to do periodically.
If I close this question now and add comments to it later (When the problem starts again) will you
guys get an email about the new post?


0
 
LVL 9

Expert Comment

by:dreamyguy
ID: 20055537
damn i'm disappointed. all these inputs from all us experts and the problem goes away after automatic updates and a server reboot? :(
0
 
LVL 2

Author Comment

by:DennisPost
ID: 20057194
btw
tasklist /svc = tlist -s

Windows 2000 support tools
0
 
LVL 2

Author Comment

by:DennisPost
ID: 20240010
It's happening again guys. :-(    :-)
Users are breating down my neck again so time is short before i HAVE to restart to temporarly resolve this.

tlist - s revealed that it's the local instance that is causing the issue.

Task manage stats:
CPU Time : 10:35
Mem. usage : 798656K
Page Faults : 703252
VM Size : 878104K
Handles : 715
Threads : 48
I/O Reads : 17341100952
I/O Writes : 6597142203

I'll run my troubleshooting Profiler template for an hour and get back to you.
0
 
LVL 2

Author Comment

by:DennisPost
ID: 20240035
mastoo:
I ran both your scripts multiple times against each database.
The most lines returned were 3 and the highest CPU% was 4.7 all the rest of the CPU% were under 4.
These results seem very acceptable to me, right?
0
 
LVL 2

Author Comment

by:DennisPost
ID: 20240262
Performance monitor stats. (Only for the troublesome instance.)

%CPU Time : Avg. 88
%Priveledge : Avg. 82
%User time : Avg 2.5

Instance is using both Xeon cores.
Both boost SQL priority on windows & use windows NT fibres are NOT selected.

Could this issue be related to me using remote desktop and running queries?
0
 
LVL 21

Expert Comment

by:mastoo
ID: 20241615
Re-reading everything I need to ask something basic: When you're looking at high cpu in task manager have you looked at the process list and verified it is one of your instances of sqlservr.exe that is causing the high cpu?  I'm not clear whether your cpu numbers posted are total usage or process usage.  I was pretty confident if it is sqlservr.exe with the high usage my script would show you the sql causing it.
0
 
LVL 2

Author Comment

by:DennisPost
ID: 20241936
>>When you're looking at high cpu in task manager have you looked at the process list and verified it is one of your instances of sqlservr.exe that is causing the high cpu?
Yes that's right.

My previous post was only for the instance of SQL that is giving me troubles.

Performance monitor has been running all day. Here are the latest averages.
% CPU = 130
% User Time = 99
% Priviledge Time = 42

I have also been running Profiler for most of the day and only just stopped and save the results to a table on the same instance as the one that's giving me hassles. (Local Instance)
... oh and I'am also running the Index Tuning wizard at the moment. (Both your scripts show Index Tuning Wizard running  > 100% CPU.

0
 
LVL 2

Author Comment

by:DennisPost
ID: 20248267
I look at this article ( http://support.microsoft.com/kb/314530 ) to help troubleshoot at SP time error
i'm getting.
No additional information was provided with DBCC TRACEON (7300, 3604).

DBCC SQLPERF(WAITSTATS) returns: (Shortend version, removed all 0 rows )
Wait Type                          Requests      Wait Time      Signal Wait Time
MISCELLANEOUS      1.0      0.0      0.0
LCK_M_S                            537.0      12762.0      230.0
LCK_M_U                            23.0      9796.0      15.0
LCK_M_X                            490.0      2374.0      141.0
LCK_M_IS                            2.0      31.0      0.0
LCK_M_IX                            4.0      79.0      0.0
SLEEP                           769417.0      7.6852006E+8      7.610743E+8
IO_COMPLETION      7662.0      52707.0      108.0
ASYNC_IO_COMPLETION      1272.0      126243.0      0.0
OLEDB                           8681987.0      7.9850118E+8      3.9679987E+9
RESOURCE_QUEUE      536188.0      2.2438339E+9      7.4988486E+8
ASYNC_DISKPOOL_LOCK17505.0      218.0      0.0
PIPELINE_INDEX_STAT      1.0      0.0      0.0
WRITELOG                           194037.0      2950177.0      28557.0
LOGBUFFER                           73.0      892.0      0.0
CMEMTHREAD      429.0      125.0      0.0
CXPACKET                           33668.0      141220.0      11338.0
PAGESUPP                           10.0      0.0      0.0
WAITFOR                           44.0      44514.0      44514.0
LATCH_SH                           3.0      63.0      0.0
LATCH_EX                           850.0      404.0      309.0
PAGELATCH_SH      38043.0      1186.0      1092.0
PAGELATCH_UP      15556.0      2393.0      2192.0
PAGELATCH_EX      18881.0      1531.0      1422.0
PAGEIOLATCH_SH      6896.0      59743.0      127.0
PAGEIOLATCH_UP      7452.0      124414.0      156.0
PAGEIOLATCH_EX      3793.0      70841.0      15.0
NETWORKIO                           717393.0      300259.0      0.0
Total                           1.1052217E+7      3.8147571E+9      5.4790482E+9

SPID 69 Has 2 TAB locks:
master.dbo.spt_values          TAB            Sch-S              GRANT                           Xact
tempdb.dbo.##lockinfo69       TAB            X                      GRANT                           Xact
Last TSQL command batch run : dbcc inputbuffer(69)

In SEM Process info I see:
PID                           69
User                         Me !!
DB                            Master
Status                      Runnable
Open Transactions  2
Command                 SELECT INTO
Application               MS SQLEM
Waiting type             Not waiting
CPU                          935

AND

PID                           52
User                         NT System
DB                            msdb
Status                      Sleeping
Open Transactions  0
Command                 AWAITING COMMAND
Application               SQLAgent - Alert Engine
Waiting type             Not waiting
CPU                          567719 (565 000 higher than any other)
0
 
LVL 2

Author Comment

by:DennisPost
ID: 20248315
btw,

We only have 10-15 users working on this server at any one time.

My own (Amature) VB applicaton also runs on this instance. ADO connections are made and closed 1000+ times a day by each user.
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 200 total points
ID: 20248455
Is parallel query execution disabled on the (3) instances? if not, disable that.
What are the MAX MEMORY settings for the 3 instances? the total should not be over 3.7GB allowed.

is the time when this happens regular (at the same time)?
 if yes, can you check if anything is scheduled / running at "exactly" that time?
0
 
LVL 2

Author Comment

by:DennisPost
ID: 20248605
>> Is parallel query execution disabled on the (3) instances? if not, disable that.
Not sure, where can I find this setting?

The combined max memory of all instances did indeed exceed 3.7GB. Task manager reported 450MB available. I lowered the max on each instance and now have 950MB available.

I have multiple scheduled backup tasks the run troughout the day. The CPU hogging also occurs between these jobs.
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 200 total points
ID: 20249136
>> Is parallel query execution disabled on the (3) instances? if not, disable that.
>Not sure, where can I find this setting?
in the server (instance) properties, you have a processor tab. there you see a "parallelism" panel, which by default, has "use all available processors".
change that to "use 1 processor"

that will make sure that 1 single query will never be executed using several processors at once.
at least, that is the setting I saw most effective with most OLTP databases, as setting thresholds or leaving the "use all available processors" made very often even small queries terribly slow by the overhead of make the query run multi-processor.

don't get the setting understood wrong: running several queries will still be spread over the several (virtual) CPU on your machine!
0
 
LVL 2

Author Comment

by:DennisPost
ID: 20249243
Okay. I have changed the settings on all 2 instances to only use CPU 1 and not CPU 0.
Only time will tell if this helps resolve this is permanently as it only occurs some weeks after rebooting.

Is there any possiblity that this is linked to me using RDP & SEM remotely?
Do I need to worry about those TAB locks or the 2 open transactions I mentioned earlier?

btw, thanks for taking a look at this question. Very much appreciated !! :-)
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20249449
>Okay. I have changed the settings on all 2 instances to only use CPU 1 and not CPU 0.
that's actually the wrong setting :-(
note also that these settings only get effective after restarting the instance(s).
0
 
LVL 2

Author Comment

by:DennisPost
ID: 20249624
Oops.
I looked again, this time with my eyes open. Found it!

I'll restart now. This would temporaraliy resolve the problem anyway, lets hope it resolves it permanently this time.

0
 
LVL 2

Author Comment

by:DennisPost
ID: 20328541
Things are looking good, but I'll hold off awarding points for a while longer.
0
 
LVL 2

Author Comment

by:DennisPost
ID: 20373173
Problem's back :-(

I have no new information. Everything looks the same as last time. Grrrr
0
 
LVL 21

Expert Comment

by:mastoo
ID: 20374086
I think go back to where we left off (at the time the script accurately told you it was the index tuning wizard using the cpu).  So, start with looking at task manager and verify that it is one of your sql server instances using most of the cpu.  Run that script on that instance and see what it says.
0
 
LVL 2

Author Comment

by:DennisPost
ID: 20380487
Hi,

I just ran both your scripts 20 times each. the highest cpu % was 3.2.
I ran the script mostly against a user database that is used more than any other database.
0
 
LVL 8

Assisted Solution

by:MrRobot
MrRobot earned 1000 total points
ID: 20381200
Hey Dennis,

Are you watching sql server counters on perfmon? They should give you an idea about what's happening.

Have you checked sql server activity monitor from management studio? It lists every process running in your instance with their resource usage.

Also, in the profiler try to include every counter, there should be an indication. If not, I would pass sql side, and consantrate on the server environment; hardware, or more likely software. There could be something wrong with the libraries installed. And since this problem lasts for a long time, I would consider re-installing the server.
0
 
LVL 2

Author Comment

by:DennisPost
ID: 20381313
Hi MrRobot,

How are ya?

Yes I am watching the sql server counters on prefmon. I can see that the CPU Priveledge % grows with each spike. I just don't know why and how I can find out.

I would like to try taking some of the other User DBs off line. How can I kick everyone off a DB so that I can take it off line?

I am already doing a lot of overtime to get other things done, so I would really, really perfer not to re-install SQL Server.
0
 
LVL 8

Assisted Solution

by:MrRobot
MrRobot earned 1000 total points
ID: 20394217
Thanks Dennis,

You can use
ALTER DATBASE db SET SINGLE_USER WITH ROLLBACK IMMEDIATE
to rollback any incomplete transactions and kick the other users out.

And you can try
ALTER DATABASE somedatabase SET OffLine WITH ROLLBACK IMMEDIATE
to put the db offline immediately.

So on perfmon, don't you notice a growth in the lock / full scan counts?
What about server activity monitor and sql server profiler?

0
 
LVL 2

Author Comment

by:DennisPost
ID: 20394520
Hi,

Ok I'm running System Monitor at the moment. Looking at locking.
Lock request / sec are related to the cpu spikes but
Lock Timeout / sec,  Lock Wait time, Lock Waits /sec & number of deadlocks are all 0.

As far as full scans are concerned, no correlation to the CPU spikes (Almost no scans happening at all). Index Searches / sec  on the other hand are happening on each CPU spike.
Probe & range scans are at a bare minimum.

If I am interpreting this data correctly, the I either have too many indexes, bad indexes or fragemented indexes. All of these I find strange as I have run the Index tuning wizard 2-3 times this year.

I just run DBCC SHOWCONTIG example E. from BOL
E. Use DBCC SHOWCONTIG and DBCC INDEXDEFRAG to defragment the indexes in a database
This example shows a simple way to defragment all indexes in a database that is fragmented above a declared threshold. (30.0)

Now Index Seaches / sec are continuously high. Avg. 265 / sec, Max 4025 /sec. :-(



0
 
LVL 2

Author Comment

by:DennisPost
ID: 20426263
Hi,

I had to restart the service to resolve this temporarily again.
I left perfmon running with the same tokens.
Index Searches still peak regulary, but no loss of performance.
I no longer think Index Searches have anything to do with this issue.

Any idea's?
0
 
LVL 8

Assisted Solution

by:MrRobot
MrRobot earned 1000 total points
ID: 20436830
It's unlikely index searches. More likely index searches are just a by product of the operation which requests locks. High lock requests can really really decrease the performance. The first reason I asked to check for it is that there can be a client issue which makes the client run a query which affects a big resultset, or improperly use cursor / lock types. Eg, using a non forward only / read only cursor can easily bring a server down on very frequent operations with a big result set. Once there was an issue with our client software's name lookup filter, which made a request for nearly every keypress when looking for a customer name, and did it using unnecessarily locked requests. This made a huge number of lock requests from time to time, which brought the server down on its knees.

Whether be it the client or the server side, this shows the problem is sql related and not a system / server issue. So what about profiler / activity monitor?

This can also be a component from an auditing / logging tool, like an extended sp or something, which does some operation time to time, and the exe file doesn't have to be using some noticable cpu time for this to happen. I would check the developer's faqs to be sure it's completely removed from the system with all its components.
0
 
LVL 2

Author Comment

by:DennisPost
ID: 20440081
Hi,

Profiler hasn't revealed much. But I addmit, I'm only only a novice in using it. I have created some traces based on what I have found on the net. Interpreting is a another skill all together.  From what I have seen there are some sp's that are taking a long time (High reads & duration) but only when things are running slow, otherwise things look okay. Maybe you have some tips for me?

As far as I know activity monitor is a tool in SQL 2005 and doesn't exist in SQL 200. Please corect me if I'm wrong.

I will open a support case with McAfee. Who knows, maybe that's causing the trouble. ProtectionPilot does run a lot of auditing & logging sps. These are the ones I was refering to above.
0
 
LVL 2

Author Comment

by:DennisPost
ID: 20440115
These MS articles explain situations that are similar to what I'm experiencing.
http://support.microsoft.com/kb/323213
http://support.microsoft.com/kb/835864

Unforunately their resolution is to install the latest service pack, which already is.
0
 
LVL 8

Assisted Solution

by:MrRobot
MrRobot earned 1000 total points
ID: 20440539
well, you may need to play more on the profiler. eg, you can trace locks:acquired, locks:escalation events during spikes, group the events on different columns, like DB ID, login name, object id, client process id, application name to see who or what causes those spikes, as you will get the number of events in each group. you can also save the results in an sql table and analyze profiler events using tsql.

you can install sql 2005 client tools and use them to connect to sql 2000. as the data structure used by the activity monitor exists on sql2000, you can use activity monitor on sql server 2000.

0
 
LVL 8

Expert Comment

by:MrRobot
ID: 20440545
also try sql batch completed events.
0
 
LVL 8

Assisted Solution

by:MrRobot
MrRobot earned 1000 total points
ID: 20440579
once you know which dbid makes the trouble, you can filter your events using "filter columns" to focus on that db.
0
 
LVL 8

Assisted Solution

by:MrRobot
MrRobot earned 1000 total points
ID: 20440625
ahh.. btw, it can be cached execution plans of stored procedures and indexes. I should've added this.

you said some procs takes long time to complete when the system slows down. use profiler to monitor read/write io access for those procs. if you see a considerable increase in io access on those SPs, you got it.

sp execution plans are calculated using the parameters given to the sp the first time it runs. so a sp can use different indexes different times. like, for the parameters given the first time it runs, it may produce an execution plan which will utilize an index for a table, but if the nature of the parameters change in a later call which makes the index usage impossible, the procedure may need huge table scans.

just keep an eye on the avarage io access for those procs on profiler.
0
 
LVL 8

Assisted Solution

by:MrRobot
MrRobot earned 1000 total points
ID: 20440634
you can try to use
sp_recompile <proc name>

during the spikes for each sp that slows down, and see if it will have any effect.

0
 
LVL 2

Author Comment

by:DennisPost
ID: 20440687
I'm running profiler at the moment. I'll use the trace as a baseline.
I had to appease the natives by restarting the service recently, or else they would have linched me.

Everything is running fine at the moment. When things slow down again i'll run the traces as you suggested.
In the meantime I'll download activity monitor and experiment a bit.

Thanks for the help!
0
 
LVL 2

Author Comment

by:DennisPost
ID: 20440819
about that Acivity Monitor.
Googling gave me a lot of "Big Brother" programs.

From this MS site:
http://www.microsoft.com/downloads/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

I downloaded this:
http://www.microsoft.com/downloads/details.aspx?FamilyId=79F151C7-4D98-4C2B-BF72-EC2B4AE69191&displaylang=en

Please let me know if this is the one you were refering to.
0
 
LVL 8

Assisted Solution

by:MrRobot
MrRobot earned 1000 total points
ID: 20446386
activity monitor is built into "management studio", the replacement for "query analyzer" and "enterprise manager". you can try "management studio express", which is free, or client tools from the trial version of "sql server 2005", if you don't have "sql server 2005". btw, when the trial time is over, client tools probably won't expire.
0
 
LVL 2

Author Comment

by:DennisPost
ID: 20812945
Hi,

This issue has come back a few times since I last posted.
Just now I tried to see what was happening in ProcessMonitor.
I got the dreaded Blue Screen of Death upon doubleclicking the processmonitor exe. FLTMGR.SYS being the culprit.

I will contiue to monitor all events from PID 1388 (Local SQL instance) with a duration > 1.
Because I just had to restart, I'll have to wait for the issue to raise its head again.

My applogies from the consistantly slow replies. Even though this is becomming a major problem, it is still not in my top 5 priorities. My boss keeps me occupied with all sorts of other things he deems to be more important......
Please bare with me, I still need your wisdom, guidance and especially your patience.
0
 
LVL 2

Author Comment

by:DennisPost
ID: 20813212
It didn't take longer to raise its head. :-(

SEM Locks revealed:
Object     LockType     Mode      Status      Owner     Index
TestDB    DB                S            Granted   Sess        
MutDet     TAB              IX           Granted   Sess
MutDet     Page             IX           Granted   Sess       PK_MutDet_7e6cc920
MutDet     Key               X           Granted   Sess       PK_MutDet_7e6cc920

The testdb is a restored (renamed) backup from the actual DB on the same instance.
Even though, I killed the application on my client machine, these still show up in SEM on the server, but not in SEM on the client.

........ Just got interupte by the big boss himself........ I now have other priorities than server crashes and poor performance...... :-( grrrrr.
I'll still be listening though.
0
 
LVL 2

Author Comment

by:DennisPost
ID: 20848847
How big an impact on performace should sp_setapprole have?

I am monitoring profiler duration > 50. 99% displayed is sp_setapprole, duration 150 - 210, cpu = 0.
Is this normal? Could it have something to do with the cpu usage?

My application ADO connection uses an application role.  the ADO Connection is closed after every tsql.
This means that sp_setapprole is used tens of thousand times a day.
Is this normal practice, or far from best practice?
0
 
LVL 2

Accepted Solution

by:
DennisPost earned 0 total points
ID: 22304041
Unfortunately the problem still pops up from time to time.
I personally suspect our in house software as the cause.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question