Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]
Groups > comp.databases.ms-sqlserver > #1763 > unrolled thread
| Started by | amitpatel815@gmail.com |
|---|---|
| First post | 2014-06-10 11:55 -0700 |
| Last post | 2014-06-18 13:12 -0700 |
| Articles | 3 — 3 participants |
Back to article view | Back to comp.databases.ms-sqlserver
SQL Server Performance Issue amitpatel815@gmail.com - 2014-06-10 11:55 -0700
Re: SQL Server Performance Issue Erland Sommarskog <esquel@sommarskog.se> - 2014-06-10 23:15 +0200
Re: SQL Server Performance Issue Mark D Powell <markp28665@gmail.com> - 2014-06-18 13:12 -0700
| From | amitpatel815@gmail.com |
|---|---|
| Date | 2014-06-10 11:55 -0700 |
| Subject | SQL Server Performance Issue |
| Message-ID | <fc736b42-e860-420c-bab5-19bb37f9c0a8@googlegroups.com> |
Hello Experts, One of my website is hosted on IIS and the database is on MS-SQL Server 2008 R2 and approximately 100-120 users access this portal for data entry. But my Site is responding extremely slow and sometimes users get stuck when they submit forms. And when i am checking the server status, all the hardware resources(RAM,CPU) are free I mean there is no too much cpu load or memory issue on the server and hardware configuration of my server is mentioned below. Processor:- intel i7 8-core RAM:- 8GB And on the same MS-SQL server, database of one of my other site is located and approx 300 users are doing the similar task on that other site but we are not facing any slow speed with that other site and performance of that other site is too good. And both sites are hosted on the same IIS. So, here i am confused and not sure why my site is not working smoothly while other site with more users is working absolutely fine and i have enough hardware resources on the server. Can anyone please help me or suggest me how can i resolve or troubleshoot this issue. Please let me know if you have any question. Thanks. Amit
[toc] | [next] | [standalone]
| From | Erland Sommarskog <esquel@sommarskog.se> |
|---|---|
| Date | 2014-06-10 23:15 +0200 |
| Message-ID | <XnsA348ECABE86D8Yazorman@127.0.0.1> |
| In reply to | #1763 |
(amitpatel815@gmail.com) writes:
> So, here i am confused and not sure why my site is not working smoothly
> while other site with more users is working absolutely fine and i have
> enough hardware resources on the server.
>
> Can anyone please help me or suggest me how can i resolve or
> troubleshoot this issue.
>
A start is to collect wait statistics. Run this procedure with ten minutes
in between at a time when the server is slow:
CREATE PROCEDURE wait_stats_monitor AS
SET NOCOUNT ON
DECLARE @latest datetime,
@now datetime
SELECT @now = getdate()
IF object_id('waitstats') IS NULL
BEGIN
CREATE TABLE waitstats (
sample_time datetime NOT NULL,
wait_type nvarchar(60) NOT NULL,
sample_length_sec int NULL,
delta_tasks_count bigint NULL,
delta_wait_time bigint NULL,
delta_signal bigint NULL,
max_wait_time_ms bigint NOT NULL,
tot_tasks_count bigint NOT NULL,
tot_wait_time bigint NOT NULL,
tot_signal bigint NOT NULL,
CONSTRAINT pk_waitstats PRIMARY KEY (wait_type, sample_time)
)
CREATE INDEX sample_time ON waitstats (sample_time)
END
IF NOT EXISTS (SELECT * FROM waitstats)
BEGIN
INSERT waitstats (sample_time, wait_type, max_wait_time_ms,
tot_tasks_count, tot_wait_time, tot_signal)
SELECT @now, wait_type, max_wait_time_ms,
waiting_tasks_count, wait_time_ms, signal_wait_time_ms
FROM sys.dm_os_wait_stats
END
ELSE
BEGIN
SELECT @latest = MAX(sample_time) FROM waitstats
INSERT waitstats (sample_time, wait_type, sample_length_sec,
delta_tasks_count,
delta_wait_time,
delta_signal,
max_wait_time_ms, tot_tasks_count, tot_wait_time,
tot_signal)
SELECT @now, ws.wait_type, datediff(ss, m.sample_time, @now),
ws.waiting_tasks_count - m.tot_tasks_count,
ws.wait_time_ms - m.tot_wait_time,
ws.signal_wait_time_ms - m.tot_signal,
ws.max_wait_time_ms, ws.waiting_tasks_count, ws.wait_time_ms,
ws.signal_wait_time_ms
FROM sys.dm_os_wait_stats ws
CROSS APPLY (SELECT TOP 1 *
FROM waitstats m
WHERE m.wait_type = ws.wait_type
ORDER BY sample_time DESC) m
WHERE ws.waiting_tasks_count <> m.tot_tasks_count OR
ws.wait_time_ms <> m.tot_wait_time OR
ws.signal_wait_time_ms <> m.tot_signal
END
When you have that data run this query:
SELECT TOP 10 wait_type, delta_tasks_count, delta_wait_time,
delta_signal
FROM waitstats
WHERE [wait_type] NOT IN (
N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR',
N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH',
N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
N'CHKPT', N'CLR_AUTO_EVENT',
N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',
N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE',
N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD',
N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
N'EXECSYNC', N'FSAGENT',
N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
N'HADR_CLUSAPI_CALL',
N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE',
N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',
N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP',
N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE',
N'PWAIT_ALL_COMPONENTS_INITIALIZED',
N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',
N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH',
N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP',
N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP',
N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT',
N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS',
N'WAITFOR', N'WAITFOR_TASKSHUTDOWN',
N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',
N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT')
ORDER BY delta_wait_time
Post this output here.
--
Erland Sommarskog, Stockholm, esquel@sommarskog.se
[toc] | [prev] | [next] | [standalone]
| From | Mark D Powell <markp28665@gmail.com> |
|---|---|
| Date | 2014-06-18 13:12 -0700 |
| Message-ID | <c840d834-ceb6-42fe-b4e9-981ef24bd180@googlegroups.com> |
| In reply to | #1763 |
On Tuesday, June 10, 2014 2:55:51 PM UTC-4, Amit Patel wrote: > Hello Experts, > > > > One of my website is hosted on IIS and the database is on MS-SQL Server 2008 R2 and approximately 100-120 users access this portal for data entry. > > > > But my Site is responding extremely slow and sometimes users get stuck when they submit forms. > > > > And when i am checking the server status, all the hardware resources(RAM,CPU) are free I mean there is no too much cpu load or memory issue on the server and hardware configuration of my server is mentioned below. > > > > Processor:- intel i7 8-core > > RAM:- 8GB > > > > And on the same MS-SQL server, database of one of my other site is located and approx 300 users are doing the similar task on that other site but we are not facing any slow speed with that other site and performance of that other site is too good. > > > > And both sites are hosted on the same IIS. > > > > So, here i am confused and not sure why my site is not working smoothly while other site with more users is working absolutely fine and i have enough hardware resources on the server. > > > > Can anyone please help me or suggest me how can i resolve or troubleshoot this issue. > > > > Please let me know if you have any question. > > > > Thanks. > > Amit Amit, is your slowly performing application querying the same tables in the same database as the good performing application? If not, have you checked your indexing? HTH -- Mark D Powell --
[toc] | [prev] | [standalone]
Back to top | Article view | comp.databases.ms-sqlserver
csiph-web