Friday, May 25, 2018

Add a custom report to SSMS

Maybe some of you don't know this, but to view Report Definition Language reports (RDL) , you aren't forced to use SQL Server Reporting Services and a report server running on the machine.

With the latest version of SSMS you are able to view any report, just have in mind 2 things when creating your report to be able to use it on SSMS:
  1. Use an embedded Dataset for your report and point the server name to (local)
  2. Add the database name to your objects if you want a specific db, otherwise, SSMS will use the current db or the master as default.
You can create RDL reports (among other projects) with the free tool SQL Server Data Tools (SSDT) who is the replacement for the old Business Development Integration Studio(BIDS).

Once you have your report created, it is just matter of importing it to SSMS to use it. I will show you how to do it.

Add the report to SSMS

For this example I am using one of my open source reports for server monitoring.

First, connect to your server using SSMS and right click on the server or database, then select Reports>Custom Reports...

Locate the report file on your windows explorer. Note: If you want another name for the report, rename the file on this point, then select it and click OPEN.

A Warning will appear for any custom report you add, and again I tell you: Always review the source of any custom code before using it to avoid malicious executions against your servers, also test them in a non-prod server before. As advice: trust no one.
If you trust on the source, then ignore the warning and click RUN.

After that, the custom report will appear, note that the connection has been changed to the current server where the report is being executed.
BE CAREFUL: Report runs with the credentials of the user connected to the SSMS at that point, so have this in mind for sensitive information or object permissions.

After that, the report will be available for you to execute it at any time.

To Delete it

Reports are not "imported" to SSMS, so if you want to delete it, just delete the rdl file on the windows explorer, after that if you select it again in SSMS it will prompt to delete it from recent list.

Wednesday, May 16, 2018

Query to determine last statistics update for a database

Statistics are a vital part of SQL Server database engine, these are used for the query optimizer to create query plan.
These statistics store distribution information about one or more columns on tables and views, you can read more about how statistics works from Microsoft documentation in the first link.

On this post I am sharing a lightweight query to determine last update statistics date for each user table on a database. Please note that objects with null values are objects without statistics.
the ones with _WA_Sys names, are the ones generated automatically when you have enabled the AUTO_CREATE_STATISTICS option.

SELECT [table name],
 sp.stats_id, [stat Name],
 sp.rows [object rows],
FROM sys.objects obj
LEFT JOIN sys.stats AS stat --Include objects without statistics
 ON obj.object_id = stat.object_id
OUTER APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
WHERE obj.type = 'U' -- Only user tables
ORDER BY [table name],

Remember that you can update them using the UPDATE STATISTICS option.

Monday, May 14, 2018

Quick charts in SQL Server Operations Studio

Sample chart for quick insights
SQL Server Operations Studio will be a new tool for managing SQL Server, Azure SQL Database, and Azure SQL Data Warehouse, this tool is not meant to replace SSMS, and is focused more to Database Developers than for Database Administrators.

On this post I want to share with you an option to create quick charts from the results window.

View data as chart

Open SQL Server Operations Studio and select a new query (Ctrl + N)

Once you have your new query window and you are connected to your server, write your query (just note that since this is a quick function, it won't work properly for complex charts)

Run your query clicking the run button or hitting F5 (Ctrl +F5 for selected text) and once the results are displayed, select the View as Chart option in the right of the results window

When you click this option, an horizontal bar chart will open by default, but you can change the chart type on the right side

Also if you hover the mouse over a point, it will display the value.
You can save the chart also if you want or create an Insight template with the options above the chart.

And that's all. As you can see, the customization options are very limited, but the product is still in preview, so new features can come in newer releases.

If you haven't download the SQL Server Operations Studio, you can do it from Microsoft Site here

More info:

What is Microsoft SQL Operations Studio (preview)?

Tuesday, May 8, 2018

Quick tip: Refresh IntelliSense cache in SSMS

If you work with multiple windows or make a change using SSMS to an object, sometimes IntelliSense does not reflect the changes in your code, to update recent changes you need to refresh the local cache.

This option is located in SSMS, under Edit>IntelliSense>Refresh Local Cache or just hitting Ctrl+Shift+R