Back up and Performance issue

Question

Back up and Performance issue

sam-1083699

SSC Eights!

Points: 873
More actions
June 24, 2010 at 12:35 pm

#220354

HI Friends,
I'm currently working on a server where lot of transactions will be running and this server is a very beefy server with 4 quadcore processes for a total of 16cores running at 3.33GHz with 10GB ram.
When i try to get a back of a particular database it gave a .bak file of 13GB. when i run sp_spaceused on this current database i got
Database size: 25019 MB
Unallocated space: 2773
reserved : 13745976KB
Data: 8466880 KB
Indexsize: 5189432 KB
unused: 89664 KB
I just wanna make sure that the backup which i got is okay?
I have a performance issue when i run a particular stored procedure it's taking almost 10min to execute (only this particular procedure) but when i run the same procedure on different server it just taking like <2min.
Can you please suggest me some ideas.
Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

sam-1083699 (6/24/2010)
HI Friends,
I'm currently working on a server where lot of transactions will be running and this server is a very beefy server with 4 quadcore processes for a total of 16cores running at 3.33GHz with 10GB ram.
When i try to get a back of a particular database it gave a .bak file of 13GB. when i run sp_spaceused on this current database i got
Database size: 25019 MB
Unallocated space: 2773
reserved : 13745976KB
Data: 8466880 KB
Indexsize: 5189432 KB
unused: 89664 KB
I just wanna make sure that the backup which i got is okay?

the data and index add up to be your 13 gig, so the size of the backup is fine; the the other half o f the space, the 13 gig of reserved/unused is required to restore(25+ gig of total space), and is noted inside the backup, but doesn't require real space to store it.

I have a performance issue when i run a particular stored procedure it's taking almost 10min to execute (only this particular procedure) but when i run the same procedure on different server it just taking like <2min.
Can you please suggest me some ideas.
Thanks.

slow procs are often affected by out of date statistics, or sometimes parameter sniffing; this is especially a probablility when you say it runs good one place but not another.

show us the proc, maybe wer can offer suggestions.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 2

I'd concur with Lowell's thoughts. Start a thread with the performance issue, include an exec plan.

For the backup, restore it somewhere to check, but it should be fine.

AND you SHOULD have free space in your database.

sam-1083699 SSC Eights! Points: 873 More actions · Answer 3

This is the procedure which was written by other developers. iam thinking to change Let me know your ideas...

Thanks.

Lowell SSC Guru Points: 323518 More actions · Answer 4

well i saw one thing we can fix right away so far:

there is a scalar function dbo.Get_LocalDateTime that is called multiple times; that needs to be changed to a inline table valued function;

scalar funcitons a re called once per row...so call it on a million row table, and it'll slow things down.

paste a copy of the function and i'll change it for you and we will see if we can give you back your proc with the correct function calls.

that will help a bit while we look at the rest of it.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sam-1083699 SSC Eights! Points: 873 More actions · Answer 5

Hi Lowell,

Thanks for your information. Just to let you know I am running the same function and same procedure on other system and getting the results in 30 secs. I am trying to figure out the reasons. Do you have any options? any way here is the code of my scalar function. Let me know your ideas.

Thanks.