After capturing the data in the tables ( link here - http://sql-dba-online.blogspot.com/2015/02/performance-monitor-how-to-run-relog-to.html) The following script will help to analyze the data.
/*show data collected for specific collector set, ordered by counter and time */
SELECT * from counterData cd
JOIN DisplayTOId di
ON cd.[GUID] = di.[GUID]
JOIN counterdetails cdt
ON cdt.[counterID] = cd.[counterID]
WHERE di.[DisplayString] = 'ProdSample.blg'
ORDER BY cdt.[ObjectName],
cdt.[CounterName],cd.[RecordIndex]
/* List max,min,avg,stdev for each counter for a specific collector*/
SELECT CONVERT(VARCHAR(10),cd.counterDateTime, 101) AS "collection" ,
RTRIM(cdt.objectName) + '\' + rtrim(cdt.CounterName) +
Case
WHEN cdt.InstanceName IS NULL then ''
WHEN cdt.instanceName is not null then '_' + rtrim(cdt.InstanceName)
END as "Counter",
CAST (MIN(cd.CounterValue) AS Decimal(6,0)) AS "Minimum",
CAST (MAX(cd.CounterValue) AS Decimal(6,0)) AS "Maximum",
CAST (AVG(cd.CounterValue) AS Decimal(6,0)) AS "Average",
CAST (STDEV(cd.CounterValue) AS Decimal(6,0)) AS "StDev"
FROM counterData cd
JOIN DisplayTOId di
ON cd.[GUID] = di.[GUID]
JOIN counterdetails cdt
ON cdt.[counterID] = cd.[counterID]
WHERE di.[DisplayString] = 'ProdSample.blg'
GROUP BY CONVERT(VARCHAR(10),cd.counterDateTime, 101),cdt.objectName,cdt.CounterName,cdt.instanceName
Wednesday, February 18, 2015
Performance Monitor : How to run relog to process performance monitor data
Run relog from the directory where the datacollector file is
Type the following in the command prompt
c:\cd PerfLogs\Admin\FromTemplate\000001
( the above is the path where my file is)
running relog
c:\cd PerfLogs\Admin\FromTemplate\000001 > relog ProdSample.blg -f SQL -o SQL:BaselineData!ProdSample.blg
ProdSample is my perflog file
-f is the format in which you want
-o is the output
SQL:BaselineData ( it is the ODBC connection that I had created pointing to the database where I wanted the data to be dumped)
Once I ran the above command relog transferred the data and it created the following tables in the database connected trough ODBC . BaseLinedata in this case.
DisplayToID
CounterData
CounterDetails
Type the following in the command prompt
c:\cd PerfLogs\Admin\FromTemplate\000001
( the above is the path where my file is)
running relog
c:\cd PerfLogs\Admin\FromTemplate\000001 > relog ProdSample.blg -f SQL -o SQL:BaselineData!ProdSample.blg
ProdSample is my perflog file
-f is the format in which you want
-o is the output
SQL:BaselineData ( it is the ODBC connection that I had created pointing to the database where I wanted the data to be dumped)
Once I ran the above command relog transferred the data and it created the following tables in the database connected trough ODBC . BaseLinedata in this case.
DisplayToID
CounterData
CounterDetails
Wednesday, February 11, 2015
How do I use parameters in an SSIS ADO NET source?
How do I use parameters in an SSIS ADO NET source -
Question
When I use SQL Command Text, there is a Parameters button in OLE DB Source. But now I want to use ADO NET Source in Data Flow and I cannot find such button in ADO NET source. How do I pass parameters to an ADO NET Source?
Answer
You can use variables together with Expression to pass the parameters:
1) Switch to Control Flow, click the Data Flow Task.
2) In the Properties window, notice a property called Expression and a small button next to it. Click the button to open the Expression Editor.
3) In the Property list, select [The name of ADO NET Source].[SqlComamnd] and click the button under expression column to open Expression Builder.
4) Write the query with variable names and click the Evaluate Expression button to test the expression. For example:
"SELECT * FROM EMPLOYEES WHERE FNAME='" + @[User::FName] + "'"
"SELECT * FROM EMPLOYEES WHERE FNAME='" + @[User::FName] + "'"
Friday, February 6, 2015
Friday, January 16, 2015
script:User Permission
The following script creates a new user and lets the user create new objects and grant permission on the objects to other users
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'user1')
DROP USER [user1]
create user [user1] for login [domain\user1]
EXEC sp_addrolemember db_ddladmin, [user1]; -- allows to create objects
EXEC sp_addrolemember db_securityadmin, [user1]; --allows to assign permission on objects
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'user1')
DROP USER [user1]
create user [user1] for login [domain\user1]
EXEC sp_addrolemember db_ddladmin, [user1]; -- allows to create objects
EXEC sp_addrolemember db_securityadmin, [user1]; --allows to assign permission on objects
Thursday, January 15, 2015
suspect pages errors
USE msdb
GO
SELECT * FROM dbo.suspect_pages
Event_type column gives different values
Errors Recorded in suspect_pages Table
The suspect_pages table contains one row per page that failed with an 824 error, up to a limit of 1,000 rows. The following table shows errors logged in the event_type column of the suspect_pages table.
The suspect_pages table also records transient errors. Sources of transient errors include an I/O error (for example, a cable was disconnected) or a page that temporarily fails a repeated checksum test.
How the Database Engine Updates the suspect_pages Table
The Database Engine takes the following actions on the suspect_pages table:
---To clean up the suspect_pages table run the following
delete s
from msdb.dbo.suspect_pages s
where event_type in (4,5,7)
GO
SELECT * FROM dbo.suspect_pages
Event_type column gives different values
Errors Recorded in suspect_pages Table
The suspect_pages table contains one row per page that failed with an 824 error, up to a limit of 1,000 rows. The following table shows errors logged in the event_type column of the suspect_pages table.
| Error description | event_type value |
|---|---|
| 823 error caused by an operating system CRC error or 824 error other than a bad checksum or a torn page (for example, a bad page ID) | 1 |
| Bad checksum | 2 |
| Torn page | 3 |
| Restored (The page was restored after it was marked bad) | 4 |
| Repaired (DBCC, AlwaysOn, or mirroring repaired the page) | 5 |
| Deallocated by DBCC | 7 |
The Database Engine takes the following actions on the suspect_pages table:
- If the table is not full, it is updated for every 824 error, to indicate that an error has occurred, and the error counter is incremented. If a page has an error after it is fixed by being repaired, restored, or deallocated, its number_of_errors count is incremented and its last_update column is updated
- After a listed page is fixed by a restore or a repair operation, the operation updates the suspect_pages row to indicate that the page is repaired (event_type = 5) or restored (event_type = 4).
- If a DBCC check is run, the check marks any error-free pages as repaired (event_type = 5) or deallocated (event_type = 7).
---To clean up the suspect_pages table run the following
delete s
from msdb.dbo.suspect_pages s
where event_type in (4,5,7)
Wednesday, January 14, 2015
Script: List all foreign keys in a database
SELECT
o1.name AS FK_table,
c1.name AS FK_column,
fk.name AS FK_name,
o2.name AS PK_table,
c2.name AS PK_column,
pk.name AS PK_name,
fk.delete_referential_action_desc AS Delete_Action,
fk.update_referential_action_desc AS Update_Action
FROM sys.objects o1
INNER JOIN sys.foreign_keys fk
ON o1.object_id = fk.parent_object_id
INNER JOIN sys.foreign_key_columns fkc
ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.columns c1
ON fkc.parent_object_id = c1.object_id
AND fkc.parent_column_id = c1.column_id
INNER JOIN sys.columns c2
ON fkc.referenced_object_id = c2.object_id
AND fkc.referenced_column_id = c2.column_id
INNER JOIN sys.objects o2
ON fk.referenced_object_id = o2.object_id
INNER JOIN sys.key_constraints pk
ON fk.referenced_object_id = pk.parent_object_id
AND fk.key_index_id = pk.unique_index_id
ORDER BY o1.name, o2.name, fkc.constraint_column_id
o1.name AS FK_table,
c1.name AS FK_column,
fk.name AS FK_name,
o2.name AS PK_table,
c2.name AS PK_column,
pk.name AS PK_name,
fk.delete_referential_action_desc AS Delete_Action,
fk.update_referential_action_desc AS Update_Action
FROM sys.objects o1
INNER JOIN sys.foreign_keys fk
ON o1.object_id = fk.parent_object_id
INNER JOIN sys.foreign_key_columns fkc
ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.columns c1
ON fkc.parent_object_id = c1.object_id
AND fkc.parent_column_id = c1.column_id
INNER JOIN sys.columns c2
ON fkc.referenced_object_id = c2.object_id
AND fkc.referenced_column_id = c2.column_id
INNER JOIN sys.objects o2
ON fk.referenced_object_id = o2.object_id
INNER JOIN sys.key_constraints pk
ON fk.referenced_object_id = pk.parent_object_id
AND fk.key_index_id = pk.unique_index_id
ORDER BY o1.name, o2.name, fkc.constraint_column_id
Tuesday, January 13, 2015
Diskspace analysis, database growth (yearly)
I am using the following query to forecast the yearly database growth
select cast(MAX(Space_Used_in_MB)as float) as max_size , min(Space_Used_in_MB) AS MIN_SIZE ,
cast((MAX(Space_Used_in_MB) - MIN(Space_Used_in_MB)) as decimal(18,2))/ MIN(Space_Used_in_MB) * 100 as [yearly percent growth] ,
Database_Name from SQLMonitor..SQLAdmin_Monitor_db_file_info
where FILE_ID = 1
and Time_collected >='01/13/2014'
group by Database_Name
order by database_name
--SQLMonitor is a user database
--SQLAdmin_Monitor_db_file_info is a user table where a job ( code here) logs the database file sixe in every four hours
-- if the time frame you want to measure is two years change the time_collected to '01/13/2013' and change the yearly percent growth calculation as follows
( cast((MAX(Space_Used_in_MB) - MIN(Space_Used_in_MB)) as decimal(18,2))/ MIN(Space_Used_in_MB) * 100 )/2
Linear: Current disk space + (growth * No of periods)
Compound: Current disk space * (1 + Growth %) ^ No of periods
Geometric: Current growth + [initial increment * {1-Incrementgrowth rate^ (No of period+1)}]/ (1 – growth rate)
To illustrate the fact, let us take one example. Say for, you have a database and it is of 100 GB. If it grows at 10% per year, then after 2 years, it will be 100 + (10 * 2) = 120 GB (linear growth).
If it is so that the database grows at 5% rate/month for 2 years, then it will be
100 * (1 + 0.05) ^ 24 = 322.5 GB (compound growth).
Take another case. Say for you have already a database of 100GB. Now the growth rate is so that it starts at 10 GB/month and the increment itself increments at 5%/month, then in 24 months, it will be 100 + {(10 * (1- (1.05 ^ (24 + 1))) / (1-1.05)} = 100 + 477 = 577 GB (geometric growth).
select cast(MAX(Space_Used_in_MB)as float) as max_size , min(Space_Used_in_MB) AS MIN_SIZE ,
cast((MAX(Space_Used_in_MB) - MIN(Space_Used_in_MB)) as decimal(18,2))/ MIN(Space_Used_in_MB) * 100 as [yearly percent growth] ,
Database_Name from SQLMonitor..SQLAdmin_Monitor_db_file_info
where FILE_ID = 1
and Time_collected >='01/13/2014'
group by Database_Name
order by database_name
--SQLMonitor is a user database
--SQLAdmin_Monitor_db_file_info is a user table where a job ( code here) logs the database file sixe in every four hours
-- if the time frame you want to measure is two years change the time_collected to '01/13/2013' and change the yearly percent growth calculation as follows
( cast((MAX(Space_Used_in_MB) - MIN(Space_Used_in_MB)) as decimal(18,2))/ MIN(Space_Used_in_MB) * 100 )/2
Growth of database ( http://www.sqlservercentral.com/articles/Administration/analyzingdiskcapacityforsqlserver/2467/)
Database growth can be of three types:- linear growth (grows at a constant amount over time)
- compound growth (grows at a constant rate for a specific period of time)
- geometric growth (grows periodic by some increment, which is in turn growing at a constant rate)
Linear: Current disk space + (growth * No of periods)
Compound: Current disk space * (1 + Growth %) ^ No of periods
Geometric: Current growth + [initial increment * {1-Incrementgrowth rate^ (No of period+1)}]/ (1 – growth rate)
To illustrate the fact, let us take one example. Say for, you have a database and it is of 100 GB. If it grows at 10% per year, then after 2 years, it will be 100 + (10 * 2) = 120 GB (linear growth).
If it is so that the database grows at 5% rate/month for 2 years, then it will be
100 * (1 + 0.05) ^ 24 = 322.5 GB (compound growth).
Take another case. Say for you have already a database of 100GB. Now the growth rate is so that it starts at 10 GB/month and the increment itself increments at 5%/month, then in 24 months, it will be 100 + {(10 * (1- (1.05 ^ (24 + 1))) / (1-1.05)} = 100 + 477 = 577 GB (geometric growth).
Perfmon helpful couters
Buffer cache hit ratio :
It is the percentage of data server had in the cache and did not have to read it from the disk.
Buffer cache hit ratio should be more than 90% for a SQL Server
SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 as BufferCacheHitRatio
FROM sys.dm_os_performance_counters a
JOIN (SELECT cntr_value,OBJECT_NAME
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio base'
AND OBJECT_NAME = 'SQLServer:Buffer Manager') b ON a.OBJECT_NAME = b.OBJECT_NAME
WHERE a.counter_name = 'Buffer cache hit ratio'
AND a.OBJECT_NAME = 'SQLServer:Buffer Manager'
Page Life Expectancy (PLE) :
It is the number of seconds the data will stay in the cache. The ideal value is 300 if less than 300 or between 300-400 the sever will require more memory.
SELECT *,cntr_value as [PLE in secs],cntr_value / 60 as [PLE in mins],
cntr_value / 3600 as [PLE in hours],
cntr_value / 86400 as [PLE in days]
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page life expectancy'
AND OBJECT_NAME = 'SQLServer:Buffer Manager'
It is the percentage of data server had in the cache and did not have to read it from the disk.
Buffer cache hit ratio should be more than 90% for a SQL Server
SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 as BufferCacheHitRatio
FROM sys.dm_os_performance_counters a
JOIN (SELECT cntr_value,OBJECT_NAME
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio base'
AND OBJECT_NAME = 'SQLServer:Buffer Manager') b ON a.OBJECT_NAME = b.OBJECT_NAME
WHERE a.counter_name = 'Buffer cache hit ratio'
AND a.OBJECT_NAME = 'SQLServer:Buffer Manager'
Page Life Expectancy (PLE) :
It is the number of seconds the data will stay in the cache. The ideal value is 300 if less than 300 or between 300-400 the sever will require more memory.
SELECT *,cntr_value as [PLE in secs],cntr_value / 60 as [PLE in mins],
cntr_value / 3600 as [PLE in hours],
cntr_value / 86400 as [PLE in days]
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page life expectancy'
AND OBJECT_NAME = 'SQLServer:Buffer Manager'
Tuesday, January 6, 2015
Reasons for database to go to SUSPECT mode
Possibility 1 : Someone deleted or misplaced a log/data file when SQL server was offline so the database could not start as it is missing a file.
Solution : Put the missing file back on the location. SQL error log will give the exact location of the missing file.
Possibility 2 : SQL server could not access to put an exclusive lock on the data/log file while the database was starting up . This could be caused when the data SQL server is shared with some other tool ( e.g. antivirus)
Solution: Find the file handler that placed an exclusive lock on the data/log file and kill it. Then run the following query.
RESTORE DATABASE WITH RECOVERY
Possibility 3 : Due to a corrupted transaction. This could likely be that the SQL server went down in the middle of the transaction and while coming back it could not complete ( COMMIT/ROLLBACK) the transaction.
Solution: Switch to emergency mode and run the dbcc checkdb command
Possibility 4: Some OS/hardware failure
Solution : Put the missing file back on the location. SQL error log will give the exact location of the missing file.
Possibility 2 : SQL server could not access to put an exclusive lock on the data/log file while the database was starting up . This could be caused when the data SQL server is shared with some other tool ( e.g. antivirus)
Solution: Find the file handler that placed an exclusive lock on the data/log file and kill it. Then run the following query.
RESTORE DATABASE WITH RECOVERY
Possibility 3 : Due to a corrupted transaction. This could likely be that the SQL server went down in the middle of the transaction and while coming back it could not complete ( COMMIT/ROLLBACK) the transaction.
Solution: Switch to emergency mode and run the dbcc checkdb command
Possibility 4: Some OS/hardware failure
Friday, January 2, 2015
Batch Update : using TOP n
---do not use set rowcount.
--Use top n
WHILE (1=1)
BEGIN
BEGIN TRANSACTION
UPDATE r
SET col1= col2
from Table1 r
where r.col3 in (select top 10000 col3 from Table1 where col1 is null)
-- Update 10000 nonupdated rows
IF @@ROWCOUNT = 0
BEGIN
COMMIT TRANSACTION
BREAK
END
COMMIT TRANSACTION
END
--Use top n
WHILE (1=1)
BEGIN
BEGIN TRANSACTION
UPDATE r
SET col1= col2
from Table1 r
where r.col3 in (select top 10000 col3 from Table1 where col1 is null)
-- Update 10000 nonupdated rows
IF @@ROWCOUNT = 0
BEGIN
COMMIT TRANSACTION
BREAK
END
COMMIT TRANSACTION
END
Subscribe to:
Posts (Atom)