Thursday, June 21, 2018

What it is Page Life Expectancy (PLE)

Which cup will you prefer to go the less
number of times to the coffee maker?
image was taken from here
Let us imagine this: Every morning when you get to the office, you must drink coffee to be fully awake, if you are a coffee lover, maybe you should have your own personalized 24 oz cup to put it.
Let's say one day you lose your cup and you have to grab one of the tiny-ugly cups available in the office, now you have to put the coffee on it several times to drink the same 24 oz, this means you will have to leave your desk several times.

Something similar happens with Page Life Expectancy (PLE). This is a measure that indicates us how long a data page stays in memory(in seconds) so it can be reused by other SQL Processes without having to read the same page from disk again (which translates in less I/O and less overhead). So with this in mind, the higher the value the better, but, how much is high enough for our system?


Right PLE value for my SQL Server


Long time ago, when servers were not so powerful, the recommendation was to have our PLE higher than 300, but now times have changed and servers too, so the recommendation is to have a PLE higher than 300 for each 4 GB of RAM assigned to SQL Server if you have Max server memory set (or for your server memory if you don't have a maximum set to SQL Server).

Keep in mind that you have to measure this value over a period of time and then average it to have a more consistent value or to identify sudden drops and correlate them with queries executed at that time.

To Calculate it


I take it from performance counters, more specifically from the Buffer Manager counter.
To calculate it I use the following T-SQL (please note that this only gives you the current value):


DECLARE @SQLmemory NUMERIC(9, 2)
DECLARE @Maxmemory INT

SET @SQLmemory = (
  SELECT physical_memory_kb / 1024.0 / 1024.0
  FROM sys.dm_os_sys_info
  )
SET @Maxmemory = (
  SELECT convert(INT, value_in_use)
  FROM sys.configurations
  WHERE [name] = 'max server memory (MB)'
  )

IF (@Maxmemory) <> 2147483647
BEGIN
 SET @SQLmemory = @Maxmemory / 1024.0
END

SELECT [counter_name]
 ,@SQLmemory AS [Server Memory_GB]
 ,cast([cntr_value] AS INT) AS [PLE_Current]
 ,(@SQLmemory / 4) * 300 AS [PLE_Expected]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Manager%'
 AND [counter_name] = 'Page life expectancy'

What if the value is too low?


This means SQL Server has to flush memory very often. this could be a symptom that your SQL Server is struggling with the currently allocated memory, so you need to tune your Database checking (but not limited to):
  • Check your indexes (missing or unused)
  • Select statements that return data you don't need
  • Tune your queries
  • Check your statistics
  • Check for ad-hoc queries
If all of the above does not work then you should consider expanding the memory of the server.


There are 2 posts I want to recommend to for further reading about this topic:

NUMA considerations for PLE from Paul Randal
Fixing PLE from Steve Hood

Monday, June 11, 2018

Collation considerations to create robust queries

Is your code "This" robust?
What is Collation? In short words: are the sorting rules, case, and accent sensitivity properties for your data. (if you want to read more, click here).


If you are dedicated to database development or you provide support to a lot of user databases, then you must be aware of the collation of the database at the moment to write your queries to avoid execution failures at deploy stage.


On this post, I will show you some of the most common errors related to collation and how to avoid them to have solid T-SQL queries.


1. Your DEV machine (all defaults)


If you just install SQL Server in your dev machine and use all the defaults, including collation, it is very likely you won't have any issues of this kind, so let us take the following TSQL as an example:

USE School;

--Current database and master 
SELECT name, collation_name FROM sys.databases
where database_id = DB_ID() or database_id =1; 


SELECT DEP.* 
FROM Department dep

It will execute without any issues:


So now, let us change the Collation for the School database:


USE [master];

ALTER DATABASE [School] COLLATE SQL_Latin1_General_CP1_CS_AS;


Collations are different now

After this change, the fun begins...

2. The column prefix '<something>' does not match with a table name or alias name used in the query


Executing the same tsql code, now with a different collation will raise an error:



To solve this, verify that the case on the alias, object and properties are the same, and change it to be the same across all your query:




3. The multi-part identifier "<something>" could not be bound


Our second error, if we add a WHERE clause to the query, again with a different case, an error will show:



To solve this, as the previous error, verify that the case on the alias, object and properties are the same, and change it to be the same across all your query:



4. Cannot resolve the collation conflict between "<Collation 1>" and "<Collation 2>" in the equal to operation


For our third error, we will use a different query that use tables for different databases (and maybe different collations):


SELECT QUOTENAME(SU.[name]) AS UserName 
          ,QUOTENAME(SP.[name]) AS PrincipalName 
FROM sys.sysusers AS SU -- Table located on the database
LEFT JOIN sys.server_principals AS SP -- Table located on [master]
ON SU.[name]  = SP.[name] 
AND SP.[type] = 'S';

Even when we have applied the case rules we explained before, we have another kind of error when databases use different collation:



To solve this, you must apply the same collation to both sides of the join, using COLLATE DATABASE_DEFAULT as you can see here:



One important thing, you only have to do this change where you use multi-database joins, if your joins are in the same database, this is not necessary.


Now you have the knowledge to build more robust T-SQL queries that can be deployed to a wide set of different environments and configurations and you have more probabilities they will not fail.

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 obj.name [table name],
 sp.stats_id,
 stat.name [stat Name],
 stat.filter_definition,
 sp.last_updated,
 sp.rows [object rows],
 sp.rows_sampled,
 sp.unfiltered_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],
 last_updated

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



Monday, April 30, 2018

Security: Create and view Audit Logs

It is important to have a secure environment, enforcing the least privilege principle in your servers and databases, but this can be difficult to achieve on large environments, where a lot of people have elevated privileges or several people have permissions to perform tasks over your data or schema.

As an additional measure, an audit log should be implemented in those scenarios and review these often.

SQL Server provides the functionality to implement audit logs at Server and Database level. Starting SQL Server 2008, all SQL Server editions support Server level audits. Database audits are supported beginning with SQL Server 2016 (13.x) SP1. Prior to that, database level auditing was limited to Enterprise, Developer, and Evaluation editions.

On this post I will show you how to configure and view audit logs:


Implement Audit Log


Under security, go to Audits and select New audit...


Specify a name for the audit and select an audit destination (I personally prefer a file location).
Select the desired parameters and click OK.


The Audit is now created, for it to work just enable audit.
NOTE: before making any changes to the audit configuration, you will need to disable it first.



Implement Server Level Audit


We have created our audit location, now we will proceed to create the actions we want to audit at server level.

Under Security folder, locate  Server Audit Specifications, right click and select New Server Audit Specification...


In the next window, Specify a name for the audit (you can have more than one) and select the audit created in the previous step.



Choose the action or actions to be audited. To check what is included on each action, please read here from Microsoft documentation.

Once you are done click OK to create the audit specification.
For this example I just have included the DBCC action to be audited (as we will see later on this post)


The Audit specification is also created disabled, to make it work, enable it as shown in the image.


NOTE: before making any changes to the audit specification, you will need to disable it first.


Implement Database Level Audit


The process is relatively the same to create a database level audit specification, the main difference is that the option is located under the Security folder on each database.

Right click on the Database Audit Specification folder and then New Database Audit Specification...


Select the name of the Database Audit and the audit destination (the same created in first step for this example).
Another of the key differences for Database Audits is that we have to select the object or schema we want to check.
Select the actions to audit (you can see description for each one here), and then click Ok.

For this example I will audit the SELECT command over the a table named Course in a sample School database.



After the creation of the audit specification, you must enable it to run.


NOTE: before making any changes to the audit configuration, you will need to disable it first.


Review audit logs


Having the audits configured and running is just half of the work, it will worth nothing if you don't proactively review the logs often.
There are 2 options to review the logs, using SSMS and T-SQL

For this example I have executed some sentences to trigger the server and database audits configured above so we can see some data.

SSMS


To check event logs using Management Studio, just select the Audit created, right click on it and select the View Audit Logs option


The audit log will display in a SQL Server log kind type, and you can see some of the examples I have triggered



T-SQL


For using this option, you use the DMF sys.fn_get_audit_file, specifying the file location, and for file name use *.sqlaudit to include all the files.

This is the query I use for this example:


SELECT event_time,action_id,statement,database_name,server_principal_name 
FROM sys.fn_get_audit_file ('C:\temp\TestAudit_*.sqlaudit',default,default);  

The output of the query looks like this:



I have marked in yellow the actions that have been captured in the database specification.


With this info you are good to go configuring your own audits for your Mission critical servers and review them often, also you could implement an alert or automatic reporting logic to be notified on suspicious activity.

Sources:

https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions?view=sql-server-2017

How I Built RAG for My Accounting App on Bedrock and pgvector

The first version of my accounting assistant could not find a single purchase of "comida" in a database full of lunches, misse...