Posts

Showing posts from July, 2018

Extended events (XE): Create a simple XE

Image
When someone says still uses SQL Profiler. Image is taken from here An ancient, dark place, a source of many legends and histories, although some people have been able to get out alive, some others are stuck there, the locals refer to it as "The nosy one" but we know it for another name: SQL Server Profiler SQL Server introduced Extended Events since 2008 version, and was meant to replace SQL Server profiler traces (that will be deprecated in the near future), and provides a lightweight, customizable event collection platform, and you should be using it now. What is an Extended Event? According to Microsoft official documentation, is a highly scalable and configurable architecture to be able to identify and troubleshoot any performance issue, it consists of: Packages: Contains the required objects to collect and process data. Sessions:  Process to be executed to collect the data Events: Info to be collected and later analyzed. Actions: Fields common to a

PowerBI: Implement a What If parameter

Image
Our Guinea Pig Dashboard PowerBI is focused on creating interactive, analytical reports, from a lot of different data sources and with the ability to access them via mobile devices. One of the cool options that allow your reports to be interactive, is the What If parameters: they allow you to visualize how your data could/should behave under different scenarios. On this post, I will show you a super simple way to use a what if parameter. Note: This test was done using PowerBI desktop and the Import option in the data source selection. We will use a simple Dashboard representing sales by region in a specific year, as this: Let's say we want to plan next year sales, we want to add a line representing the next year target by region, for this example just as a value between 0 and 200% of this year sales. Adding the What If Parameter Click on the Modeling Tab and select the New Parameter option. A new window will open, here you configure the values f

Display SELECT query or table as HTML

Image
This post is part of #tsql2sday event :) For my current job I support a lot of SQL Servers, they come in all the flavors and sizes (old, new, patched, unpatched, big, small, dedicated, shared, built by our team, built by others... etc). In the ocean of different possible configurations, having a centralized monitoring solution is sometimes not possible for the 100% of the servers, because of different reasons (unsupported features for older versions, located in a different domain or datacenter, some weird security restrictions). Even when we have already in place a custom inventory and monitoring solution (BTW done using C#, asp.net, SQL Server and Powershell), a couple of rogue servers, needs their own special treat, most of the cases this has to do with some kind of monitoring. So, for these serves, the solution is to implement local jobs to monitor different kinds of stuff and send via email the monitoring and usage results. We could perfectly send the info in plain text,

Saving disk space by choosing the correct data type

Image
"Database is almost 4 TB in size, user is complaining on long times for DEV data refresh and high storage costs for database and backups. Full backups are taking a long time to complete, even with compression enabled.  After a quick check, we discover tables that stores data from 15 years ago, without any purge or historical archive process implemented, most of the data stored is only with logging purposes, using incorrect data types and storing a lot of nulls. After changing data types and implementing data purge processes, database size was reduced to almost 90 GB, drastically improving their application performance and reducing storage costs and refresh times." - true story... or not - Designing our databases it is something that must be done carefully and by knowing how our application will work and choosing the right data types, specially for databases that will contain high volumes of data , can drastically improve our application response times and storage r