Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have a SQL Server with about 15 databases and about 60 + users accessing any of these databases. Each database is designed to serve different data set (meaning - we have multiple roles in each database). one user can access as many as all database (1 or 2 or any number upto 15). Users typically run the workflows which includes access to multiple databases usually 3-4 databases at a time. During peak time multiple users run similar workflows accessing many databases.

Out of 15 databases, we have 5 databases with heavy load, for rest of them it is nominal load.

Recently we started experiencing high CPU and memory issues on this server.

In most of the times, single user takes most of the resources on the server (which runs work flow on any 1 of these databases - not accessing multiple databases) and other users suffers as they are not able to execute queries.

Please suggest ways to resolve these issues - we tried resource governor but not able to handle this single user case.

Let me know if you need any other information about the system.

share

migration rejected from stackoverflow.com Jul 14 at 20:22

This question came from our site for professional and enthusiast programmers. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

closed as off-topic by Mark Storey-Smith, Max Vernon, RolandoMySQLDBA, Paul White, Aaron Bertrand Jul 14 at 20:22

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Tip of the iceberg - the question or comments reveal an underlying issue that would need extensive investigation by a consultant or database vendor support team: issues like this do not fit the SE Q&A model well. For more information see this meta post." – Mark Storey-Smith, Max Vernon, RolandoMySQLDBA, Paul White, Aaron Bertrand
If this question can be reworded to fit the rules in the help center, please edit the question.

    
Please provide, processors, cores for processor, RAM and SQL Server Version. –  ZeroWorks Jun 5 at 14:47
    
Window 2008, 24 Cores, Memory : 147447 MB –  Amit Arora Jun 5 at 14:53
    
What operations are occurring during these peak times, are you building very intensive Analytical Cubes, doing ETL with 100's of millions of rows, running a complex query of 100's of millions of rows with complex logic? I would say the Functional aspect of what the User is doing here is of primary importance. –  CodeCowboyOrg Jun 5 at 15:00
    
True about the functional aspect. Is it readonly operations? Do the need fresh data? Can those who are generating the worst workload use data replicated to another server? When resources are scarce its most likely time to scale out a bit and let aggregated data being performed on a replica or using analysis services on a different server. –  JFM Jun 5 at 15:06
    
Also, how big are transaction logs and how is configure autogrow in transation logs and database files. If your databases are big check if autogrow of its files is un % and change it to fixed MB size. –  ZeroWorks Jun 5 at 15:08

1 Answer 1

With performance issue I would like to have a look at wait stats. I use query by Jonatah kehayias to actually filter down cause can you please post output of below query.

SELECT TOP 10
wait_type ,
max_wait_time_ms wait_time_ms ,
signal_wait_time_ms ,
wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms ,
100.0 * wait_time_ms / SUM(wait_time_ms) OVER ( )
AS percent_total_waits ,
100.0 * signal_wait_time_ms / SUM(signal_wait_time_ms) OVER ( )
AS percent_total_signal_waits ,
100.0 * ( wait_time_ms - signal_wait_time_ms )
/ SUM(wait_time_ms) OVER ( ) AS percent_total_resource_waits
FROM sys.dm_os_wait_stats
WHERE wait_time_ms > 0 -- remove zero wait_time
AND wait_type NOT IN -- filter out additional irrelevant waits
( 'SLEEP_TASK', 'BROKER_TASK_STOP', 'BROKER_TO_FLUSH',
'SQLTRACE_BUFFER_FLUSH','CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT',
'LAZYWRITER_SLEEP', 'SLEEP_SYSTEMTASK', 'SLEEP_BPOOL_FLUSH',
'BROKER_EVENTHANDLER', 'XE_DISPATCHER_WAIT', 'FT_IFTSHC_MUTEX',
'CHECKPOINT_QUEUE', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
'BROKER_TRANSMITTER', 'FT_IFTSHC_MUTEX', 'KSOURCE_WAKEUP',
'LOGMGR_QUEUE', 'ONDEMAND_TASK_QUEUE',
'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BAD_PAGE_PROCESS',
'DBMIRROR_EVENTS_QUEUE', 'BROKER_RECEIVE_WAITFOR',
'PREEMPTIVE_OS_GETPROCADDRESS', 'PREEMPTIVE_OS_AUTHENTICATIONOPS',
'WAITFOR', 'DISPATCHER_QUEUE_SEMAPHORE', 'XE_DISPATCHER_JOIN',
'RESOURCE_QUEUE' )
ORDER BY wait_time_ms DESC

If you want to help yourself this is a whitepaer on how to troubleshoot performance issue in SQL server 2008.

share

Not the answer you're looking for? Browse other questions tagged or ask your own question.