August 31, 2009 at 9:09 am
Hi,
We have a web application running on server class machine with the following configuration:
O/S : Window server 2003 ED SP2
RAM: 2 GB
Processor: Intel(R) Xeon(R) 3040 2 1.86GHz 1.86 GHz.
We have SQL Server 2005 on another machine.
OS: Window server 2003 ED SP2
RAM: 4 GB
Processor: Intel(R) Xeon(TM) 3GHz 2.99 GHz
Database size: 300MB
We are able to log into the application and then when we click on a link, its taking time (say 2 to 4 mins) to open new page. In the next moment if we close and click on the same link it will take time again to open the page or it will open fast. If i check the same thing on another machine at the same time, the application will be fast. The same issue wont happen every day on some desktops. One or the other users will keep complaining that the application is slow.
Can you let me know if there is anything i need to check from database side?
August 31, 2009 at 9:53 am
not enough information to give anything but a generalized guess;
There's two obvious places there is a slowdown :
if your web pages are in .NET, they are not compiled/loaded into memory until the first time they are called...then the Just-In-Time Compiler builds the dlls, and it can be slow the first time it does that, and faster later.
If you were to stop and start the web server, you would see the slowness repeat.
similarly, SQL will build execution plans and then cache them the fist time a stored procedure is called; that will make the stored proc slow the first time it is called, and faster when it is called again. it will be even faster if the data remains in the SQL server cache, and that data might drop out of the cache in as fast as a minute if not recalled again by the same proc...but it would still have the same execution plan.
you'll want to either create a server side trace or start profiling your SQL server to see if the issue is the SQL Server.
you also want to check the DMV's for slow queries; i have this one saved in my snippets, but have not used it to get relevant results yet:
once you know what is slow, you can examine specific sqls, see their execution plans and see why it is slow....adding indexes, rewriting the query, grabbing less data, contention for disk resources...there could be other reasons ..you'll need to examine each one individually to figure out the issue.
use msdb
go
if not exists (select * from sys.schemas where name = 'MS_PerfDashboard')
exec('create schema MS_PerfDashboard')
go
if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_QueryTextFromHandle'), 'IsTableFunction') = 1
drop function MS_PerfDashboard.fn_QueryTextFromHandle
go
CREATE function MS_PerfDashboard.fn_QueryTextFromHandle(@handle varbinary(64), @statement_start_offset int, @statement_end_offset int)
RETURNS @query_text TABLE (database_id smallint, object_id int, encrypted bit, query_text nvarchar(max))
begin
if @handle is not null
begin
declare @start int, @end int
declare @dbid smallint, @objectid int, @encrypted bit
declare @batch nvarchar(max), @query nvarchar(max)
-- statement_end_offset is zero prior to beginning query execution (e.g., compilation)
select
@start = isnull(@statement_start_offset, 0),
@end = case when @statement_end_offset is null or @statement_end_offset = 0 then -1
else @statement_end_offset
end
select @dbid = t.dbid,
@objectid = t.objectid,
@encrypted = t.encrypted,
@batch = t.text
from sys.dm_exec_sql_text(@handle) as t
select @query = case
when @encrypted = cast(1 as bit) then N'encrypted text'
else ltrim(substring(@batch, @start / 2 + 1, ((case when @end = -1 then datalength(@batch)
else @end end) - @start) / 2))
end
-- Found internal queries (e.g., CREATE INDEX) with end offset of original batch that is
-- greater than the length of the internal query and thus returns nothing if we don't do this
if datalength(@query) = 0
begin
select @query = @batch
end
insert into @query_text (database_id, object_id, encrypted, query_text)
values (@dbid, @objectid, @encrypted, @query)
end
return
end
go
GRANT SELECT ON MS_PerfDashboard.fn_QueryTextFromHandle TO public
go
use MASTER
GO
SELECT TOP 20 object_schema_name(qt.object_id, qt.database_id) + N'.' + object_name(qt.object_id, qt.database_id) AS 'SPName', qt.query_text , qs.total_worker_time AS 'TotalWorkerTime',
qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
qs.execution_count AS 'ExecutionCount',
ISNULL(qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()), 0) AS 'CallsPerSecond',
ISNULL(qs.total_elapsed_time/qs.execution_count, 0) AS 'AvgElapsedTime',
qs.max_logical_reads, qs.max_logical_writes,
DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'AgeInCache'
FROM sys.dm_exec_query_stats AS qs
cross apply msdb.MS_PerfDashboard.fn_QueryTextFromHandle(sql_handle, statement_start_offset, statement_end_offset) as qt
WHERE qt.database_id = db_id()
ORDER BY qs.total_worker_time DESC
Lowell
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply