Backup and heavy reads at the same time

When a database is hit with heavy reads, and at the same time a FULL backup of it is done, how will SQL give preference for priority.

Will it give better preference to the reads and tell backup to do it slowly with less available throughput or vice versa?

Thanks
LVL 6
anushahannaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RiteshShahCommented:
it is advisable that you take full backup at less traffic hours, if full backup is going on and at the same time you are execute heavy read, performance of your read will be slower.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> When a database is hit with heavy reads,

From MSDN:
"During a backup, most operations are possible; for example, INSERT, UPDATE, or DELETE statements are allowed during a backup operation."

Server would try to achieve all operations at their optimal rate and hence would utilize resources accordingly..
0
anushahannaAuthor Commented:
The reason I am asking at different random times in the day, the backups which took 15 minutes are taking more than an hour. for one particular db, we run this couple of times a day - 5am and 5 pm. Sometimes it is done at 15 minutes, and other than times an hour+. I see this pattern in other database backups, too, where at times, there is extended delay in the backup to be done.

what activity would you capture to diagnose this?

0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> what activity would you capture to diagnose this?

Run the below query to find any active connections on which you are taking backup

select * from sys.sysprocesses
where dbid = db_id('ur_db_name')

Analyzing DBCC INPUTBUFFER (spid) of all the spid's obtained above would let us know what activity is happening on the server at that point of time.
0
anushahannaAuthor Commented:
Thanks. that sounds good also to just filter out the processes running with high resource consumption (example: select * from sys.sysprocesses where dbid = 13 and cpu > 100 and physical_io >100 and memusage>1 ); but can I do this in a automatic way through code, and schedule it to run every 5 minutes or so and archive the processes running at that time. There are almost 20 processes running for this database, and by the time I copy the SPID and put it in the DBCC command, the process may have even ended and a new process started with that id.

0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> but can I do this in a automatic way through code, and schedule it to run every 5 minutes or so and archive the processes running at that time.

Yes, just configure an agent job to capture this information into a temp table and schedule it to run every 5 minutes.

>> There are almost 20 processes running for this database, and by the time I copy the SPID and put it in the DBCC command, the process may have even ended and a new process started with that id.

Yes, this might happen.
And you can use the query in this thread to overcome that..

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/d1dbb675-ce11-4797-91f5-fa4940c3b27e
0
anushahannaAuthor Commented:
That was helpful. Thanks very much.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.