How to know the Database activity

Question

Post reply

How to know the Database activity

klnsuddu

SSCertifiable

Points: 5836
More actions
November 3, 2009 at 11:37 am

#213178

Hi,
I'm assigned to maintain a 3rd party application databases in SQL Server 2005 . Currently, I do not have any idea about the Databases. Now I need to know what databases highly transactional, what tables are highly used and what tables, indexes I need to rebuild/reorganize and how frequently I need to update statistics? The databases are in Full recovery mode and Full, Diff & log backups are in -place. please give me some tips to start analyze these databases activity.
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

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

dmoldovan SSCertifiable Points: 6334 More actions · Answer 1

Are the database and the application properly documented? Is there any performance baseline available for that server?

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 2

You should be looking at the DMV sys.dm_db_index_usage_stats

http://msdn.microsoft.com/en-us/library/ms188755(SQL.90).aspx

If Your Database Compatability Level is set to 90, then you can use the Reports available in the SSMS.

Select the Database in The Object Explorer and in the Right side, In the Summary Window you must be seeing Reports and there are some reports readily available in that you can choose the Index Usage one.

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

klnsuddu SSCertifiable Points: 5836 More actions · Answer 3

Are the database and the application properly documented? Is there any performance baseline available for that server?

We do NOT have any documentation about the Databases & Aplication.

We have SQL Server 2005 and the databases are with Compatibility mode 90. I will try Bru Medishetty advcie

thanks

klnsuddu SSCertifiable Points: 5836 More actions · Answer 4

Select the Database in The Object Explorer and in the Right side, In the Summary Window you must be seeing Reports and there are some reports readily available in that you can choose the Index Usage one.

I have selected the "Memory Consumption" report from the Object explorer and noticed the value for Page life expectency as 6.

So I want to know the units for Page life expectancy valuse here is in Minutes OR Seconds? Microsoft recommends 300 seconds as the minimum value for this metric.

So Is this 6 mins (360 seconds)or 6 seconds? please see the attachment

thanks

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 5

Page Life Expectancy is a Counter in Seconds, in your case it is 6 Seconds.

Having such a lower value as indicated by you is a clear indication of Memory Problem on the SQL Server Side.

What is the Memory on your System and and How much memory is assigned to SQL Server (under the Memory Settings in the Properties page of SQL Instance)

The Page Life Expectancy Value should be as high as possible.

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

klnsuddu SSCertifiable Points: 5836 More actions · Answer 6

Thanks,

I have checked the memory consumption report after some time and its showing now as 3573.

I just check the error log and find the below message and this is happened exactly when I open the Memory consumption report

A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 6635136, committed (KB): 15041928, memory utilization: 44%.

Is Opening these reports, takes whole memory? This is the first time I'm seeing the above message in the error log for this server. Is this due to the Memory Consumption report?

Is there a way to track the history for Page life expectancy valuse and verify what happened at that time(when page life expectancy is 6, I want to know what is running at that time)

at the same time in event viewer I'm seeing the below message saying Virtual Memory is too low:

Event Type:Information

Event Source:Application Popup

Event Category:None

Event ID:26

Date:11/3/2009

Time:4:17:33 PM

User:N/A

Computer:EZPROD

Description:

Application popup: Windows - Virtual Memory Minimum Too Low : Your system is low on virtual memory. Windows is increasing the size of your virtual memory paging file. During this process, memory requests for some applications may be denied. For more information, see Help.

Please advice me, if this is a serious issue, what are the immediate steps I should take?

thanks so much

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 7

From my understanding the Server / SQL Instance must have restarted and that is the reason for the lower value of Page Life Expectancy

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

klnsuddu SSCertifiable Points: 5836 More actions · Answer 8

From my understanding the Server / SQL Instance must have restarted and that is the reason for the lower value of Page Life Expectancy

No, the sql Service is NOT restarted. We have SQL Server 2005 EE x64 with SP3 on Windows Server 2003 R2 EE with SP2. We have 16 GB of RAM and the Max memory is set to 12 GB. please see the attachmnet for Last 7 days memoery use from "Memory Consumption" report. Its showing in RED means that the Memory is NOT good?

please advice

thanks