September 3, 2009 at 10:10 pm
hi,
We have a development and production environment and we have created the same database on both the instances
And we try to execute a couple of stored procedure and taking the report from Dev server and production server.
both the servers are having same configuration
dev it is taking 6 hours time and in production it is taking more than
12 hours
could you please let us know what will be reason
we are running some analysis on both the instances
The SQL Server Processor settings are the same on both the servers
we did the followwing changes
1. Forced the SQL Server to use all the 8 processors.
2. Increased the number parallel queries that can be executed on SQL Server.
3. Cleared the buffer cache of SQL Server.
EXEC sys.sp_configure N'affinity mask', N'255'
GO
EXEC sys.sp_configure N'affinity I/O mask', N'255'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max degree of parallelism', N'8'
GO
RECONFIGURE WITH OVERRIDE
GO
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
while running the scripts we found that
finding the DELETE is experiencing a Wait Type of PAGEIOLATCH_EX.
how do we over come this issue,could you please advise.
this is happending only for one instance
Regards
SMM
September 3, 2009 at 10:22 pm
Poor performance could be due to poorly written queries. The following two articles by Gail will help you track down such queries which you can fine tune.
September 3, 2009 at 10:42 pm
In addition to the recommended articles, it would also be helpful to include the sample queries, sample data, execution plans, and table structure.
If the servers are exactly the same on the hardware, and the servers are exactly the same in the settings - then something is being missed for the query difference - with the caveat that you have cleared the cache on both systems to get equivalent run states.
Other than that, it is possible that there is heavier load on the prod server when running this query, you might have some blocking or locking occurring. You will want to check sp_who2 to see if anything else is running while this query is running.
Post the info requested and the forum will be better able to help you.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 3, 2009 at 10:47 pm
argghhh - system error and a double-post
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 4, 2009 at 1:11 am
If you want us to help optimise, please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
PageIOLatch is typically a sign of IO contention. The IO subsystem cannot return requested data fast enough and SQL waits until it does. It'll probably be accompanied by large values in the sys.dm_io_virtual_file_stats.
Did you do load tests on the IO subsystem before loading SQL? What is the underlying disk system comprised of?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 4, 2009 at 1:36 pm
Hi,
this is good article, my problem is both the servers are having same configuration ,why is it taking
more time on production server,there are no other database exist there
the same set of codes are running in dev it is taking less tiem see -7 hours and in production more than 10,total size of the db is 25 gb and free space is log drive is 30 gb and data drive 50 gb
would like to know some where some settings are missing or not
the error it shows now is ,Script is Runnable
spid kpid block waittype wait time lastwaittype waitsource
57428800x00000 PAGEIOLATCH_EX 2:1:1401586
some time is like this and when a delete statement goes as runnable state for a long time
the sp and script has many truncation of tables and temp tables..
and some time lastwaittype changes to PAGEIOLATCH_SX both cases delete statemenst are running
can any oen help to sort this out
September 4, 2009 at 1:37 pm
Hi,
this is good article, my problem is both the servers are having same configuration ,why is it taking
more time on production server,there are no other database exist there
the same set of codes are running in dev it is taking less tiem see -7 hours and in production more than 10,total size of the db is 25 gb and free space is log drive is 30 gb and data drive 50 gb
would like to know some where some settings are missing or not
the error it shows now is ,Script is Runnable
spid kpid block waittype wait time lastwaittype waitsource
57 4288 0 0x0000 0 PAGEIOLATCH_EX 2:1:1401586
some time is like this and when a delete statement goes as runnable state for a long time
the sp and script has many truncation of tables and temp tables..
and some time lastwaittype changes to PAGEIOLATCH_SX both cases delete statemenst are running
can any oen help to sort this out
September 4, 2009 at 1:55 pm
shine.mm (9/4/2009)
Hi,this is good article, my problem is both the servers are having same configuration ,why is it taking
more time on production server,there are no other database exist there
More load?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 4, 2009 at 10:04 pm
not much load,there ar elot of temp tables and truncate tables.
delete statements
September 6, 2009 at 4:25 am
hi shine,
you told us that the configuration of these two servers are exactly the same. In previous posts someone mentioned about IO problem. I think it's quite posibble. You can run sqliosim or any other io stress tool and during this you should look at perfmon disk counters ( i.e. sec/read, sec/write, avg. disk queue). You shoud check your database file size and growth settings too.
I have one question to you too - are there one other activities on these servers? (specially on their io subsystems?)
Last (but time consuming) thing you can do is to divide your big 7hr batch into smaller pieces and test them one by one. It will help you to focus on real performance problem!
good luck!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply