Archive
SQL Planner, a monitoring tool for SQL Server for DBAs & Developers
SQL Planner is a Microsoft SQL Server monitoring Software product that helps DBA or Developer to identify issues (for ex. High CPU, Memory, Disk latency, Expensive query, Waits, Storage shortage, etc) and root cause analysis with a fast and deep level of analytical reports. Historical data is stored in the repository database for as many days as you want.
About SQL Planner monitoring tool, watch the intro here:
SQL Planner has several features under one roof:
– SQL Server Monitoring
– SQL Server Backup Restore Solution
– SQL Server Index Defragmentation Report and Solution
– SQL server Scripting solution
– DBA Handover Notes Management
Features and Capabilities:
SQL Planner is mainly built for the Monitoring feature and has several metrics as below, their details are available here:
– CPU & Memory Usage reports, Expensive Query and Procedure
– Nice visualization on CPU , Memory , IO usage , expensive query details
– Performance counters Reports
– IO Usage Analysis
– Deadlock & Blockers analysis
– Always On Monitoring
– SQL Server Waits analysis
– SQL Server Agent Job Analysis
– Missing Index analysis
– Storage analysis
– SQL Error Log Scan & Report
– Receiving Alerts: There are 50+ criteria when the Notification is sent via email and
maintained in SQL Planner dashboard too, more details here.
– Handover Notes Management
Cost:
Absolutely forever free to students , teachers and for developer/ DBA in development environment.
Some screenshots of the tool capabilities:
SQL DBA – Integration Services evaluation period has expired
I got an email from one SQL Server developer that he is not able to use import/export wizard and it is failing with below error:
TITLE: SQL Server Import and Export Wizard --------------------------------------
Data flow execution failed. Error 0xc0000033:
{5CCE2348-8B9F-4FD0-9AFA-9EA6D19576A7}: Integration Services evaluation period has
expired. Error 0xc0000033: {5CCE2348-8B9F-4FD0-9AFA-9EA6D19576A7}: Integration
Services evaluation period has expired. ------------------------------
ADDITIONAL INFORMATION: Integration Services evaluation period has expired.
({5CCE2348-8B9F-4FD0-9AFA-9EA6D19576A7}) ----------------------------------------
–> Investigate:
As per the above error message its clear that the SQL Server Instance that you had installed was under Evaluation of 180 days, because you didn’t applied any Product Key. So, now how can you make it usable again? All you need is a Product key of SQL Server and installation media to start an upgrade so that you can apply the new Product Key there.
–> Fix:
1. Open the SQL Server Installation Center and click on Maintenance link, and then click on Edition Upgrade:
2. Now on the Upgrade window Click Next and you will reach the Product Key page, apply the Key and click Next:
3. On the Select Instance page, select the SQL Instance that you want to fix and Click next. It will take some time and finally you will see a final window and click Upgrade:
4. Finally you will see the successful window, click on Close button:
5. Now Restart the SQL Server Service for this Instance, and you will see it running fine.
–> Finally, go back to SSMS and now you can connect to the SQL Instance.
Database backup to Azure blob storage with SQL Server 2016+
Backup to Azure was made available with SQL Server 2012 SP1 CU2. It provided significant cost savings versus on-premises costs of onsite/offsite storage, and device maintenance and better scalability than logical drives connected to Azure machines.
But backup to Azure was comparatively slow, and the maximum backup size was 1 TB, till SQL Server 2014.
Now with SQL Server 2016+ backup to block blobs offers more cost-effective storage, performance increases with backup striping and a faster restore process, support for larger backups, up to 12 TB, as well as granular access and a unified credential story.
Backup to block blobs also supports all of the existing backup and restore features, with the exception that appends are not supported.
For detailed information please check the Quickstart guide to SQL backup and restore to Azure Blob storage service
SQL DBA – Change RECOVERY mode & SHRINK all databases at once in SQL Server
There are times when you are left with no or very less space in you SQL Server VM disks, and the main cause of this is not only the lot amount of data coming in but the heavy processing your database Server is doing, and thus filling up the entire log space.
SQL Server provides you some effective commands to shrink the database files which got inflated due to heavy processing and to make room for new data.
ALTER DATABASE <db_name> SET RECOVERY SIMPLE; DBCC SHRINKFILE (N'<log_file_name>' , 100);
But this works with one database at a time, so what if you have lots of databases? It will be tiring and time consuming to visit each DB, get the database file names and apply the shrink command. Its even slow to do the same via SSMS GUI.
Note: Do not change the recovery mode in a PROD environment unless it is really required.
With the below T-SQL statements you can generate scripts for all the databases and can run to change all database settings at once:
-- Generate SQL Script to change Recovery mode to 'SIMPLE' for all DBs: SELECT 'ALTER DATABASE [' + name + '] SET RECOVERY SIMPLE;' as SimpleRecovery4AllDBs FROM sys.databases WHERE recovery_model_desc <> 'SIMPLE' -- Generate SQL Script to Shrink log files of all DBs: SELECT 'USE ' + DB_Name(database_id) + '; DBCC SHRINKFILE (N''' + name + ''' , 100);' as ShrinkAllDBs FROM sys.master_files WHERE database_id > 4 and [type] = 1
Please let me know if you have any other way to do the same (and in more effective way) !!!
SQL DBA – Get top Tables with Stale Statistics
SQL Server Query Optimizer (QO) uses Statistics to create query plans that improve query performance. The QO uses these statistics to estimate the cardinality (number of rows) in the query result.
There are times when these STATISTICS may go stale after certain INSERT, UPDATE and DELETE operations which involves large amount of rows, not qualifying for Auto-Stats Threshold.
For more about Statistics and Threshold check on MSDN Docs link
Identify Stale Stats:
Here is a simple query to identify TOP tables that have stale statistics, so that you can create UPDATE statistics for them explicitly:
SELECT TOP 50 CONCAT(sch.name, '.', obj.name) AS 'Table Name' ,MAX(sp.last_updated) AS 'Stats Last Updated' ,MAX(sp.rows) AS 'Rows' ,MAX(sp.modification_counter) AS 'Modification Counter' FROM sys.stats st JOIN sys.objects obj ON st.object_id = obj.object_id JOIN sys.schemas sch ON obj.schema_id = sch.schema_id CROSS APPLY sys.dm_db_stats_properties(obj.object_id, st.stats_id) AS sp WHERE obj.is_ms_shipped = 0 GROUP BY CONCAT(sch.name, '.', obj.name) ORDER BY MAX(sp.modification_counter) DESC;
UPDATE Stats:
UPDATE STATISTICS dbo.table_name GO
Just Rs. 32,900 !!!
Current Visitors
StatCounter …since April 2012
Leisure blog: Creek & Trails
- An error has occurred; the feed is probably down. Try again later.
SQL with Manoj 