Monday, June 10, 2019

Importance of Clustered Index

Everybody know that it is almost always better to have Clustered Index on a table for better performance, but I want to emphasize the Importance of Clustered Index Sizewise.

As most of the developers working in "Agile", which means without any proper planning, I've created several tables without proper indexing, just couple of foreign keys.
Tables were very narrow with just a few columns and my expectations for data growth were very modest. However, after just a little while I was very surprised when my database showed huge unexpected growth and size of the data became multiple times higher than I've expected.

After very little research I've found and fixed the problem. In this post I'll describe how I've done it.

1. Identifying the Problem.

At firs I run following script to see the biggest tables in my database:
SELECT OBJECT_SCHEMA_NAME(st.object_id) + '.' + OBJECT_NAME(st.object_id) as ObjectName,
SUM(CASE WHEN st.index_id < 2 THEN st.row_count ELSE 0 END) as row_count,
SUM(CASE WHEN st.index_id < 2 THEN st.reserved_page_count ELSE 0 END)*8 as Data_Size_KB,
SUM(CASE WHEN st.index_id < 2 THEN st.used_page_count ELSE 0 END)*8 as Used_Data_Space_KB
FROM sys.dm_db_partition_stats st
LEFT JOIN sys.tables as t ON t.object_id = st.object_id
LEFT JOIN sys.indexes i ON i.OBJECT_ID = st.OBJECT_ID AND i.index_id = 1
WHERE OBJECT_SCHEMA_NAME(st.object_id) != 'sys'
GROUP BY OBJECT_SCHEMA_NAME(st.object_id), OBJECT_NAME(st.object_id), t.name
ORDER BY Data_Size_KB DESC, row_count DESC

OPTION (RECOMPILE);

Here is the Top of what I've got:

2. Data Research

As you can see from previous results, the biggest table is "tbl_Instance_WAIT_Data", where I collect data for waits in my systems. Lets take a look at the table's structure using "sp_help":

EXEC sp_help '[Data_Collection].[tbl_Instance_WAIT_Data]';


As you can see, that table has only 7 columns and data size for one row is only 44 bytes. With simple calculation we can determine that on one SQL Server database page, which is 8 Kb in size, can fit about 150 records. For the current row count of 692096 rows, expected data size is supposed to be no more than 37 Mb, but data usage exceeds 370 Mb (10 times more!) and total space usage is almost 3 Gb (80 times more!!!)

3. Simple Fix

To fix that space over usage problem we just need to create a clustered index like this:

CREATE CLUSTERED INDEX CLIX_Instance_WAIT_Data

ON Data_Collection.tbl_Instance_WAIT_Data 
([Timestamp], Instance_ID, Wait_ID);

*Just note that I use default database Fill Factor. In my case data will be sorted by Timestamp and 100% page fill factor will be OK, but in other cases you might need to reduce it to 70%-90%.

And now lets check the space size usage:

EXEC sp_spaceused '[Data_Collection].[tbl_Instance_WAIT_Data]';


As you can see, the data size has reduced to the expected number of ~37 Mb.

Monday, May 13, 2019

Error: PowerBI does not refresh report via schedule.

Recently started working closely with PowerBI and got very strange error when tried to refresh reports on a schedule.


The message actually says:
"Several errors occurred during data refresh. Please try again later or contact your administrator."

SessionID: 1b80301e-3898-417a-af9c-2e77ec490728
[0] -1055784932: Credentials are required to connect to the SQL source. (Source at SQLServerName;DBA_Pro.). The exception was raised by the IDbCommand interface.
[1] -1055784932: The command has been canceled.. The exception was raised by the IDbCommand interface.
[2] -1055784932: The command has been canceled.. The exception was raised by the IDbCommand interface.

In my case the cause of the problem was very silly thing. PowerBI Server assigned only one data source connection string to my report, while in my report I had two data sources with only the difference in a Database Name capitalization:

In order to fix that I had to open each of my queries, which referred to capitalized "DBA_PRO" database and change it to "DBA_Pro".

As soon as I fixed all queries I've got only one data source in my report:

And after re-uploading it to the server my schedule nicely updated the report:


Hope it helps a lot of people.

Monday, April 8, 2019

Remove Trailing spaces in a string or a column when TRIM/RTRIM does not work

Very often we need to remove trailing spaces from a text in a varchar variable or a column. In that case we use very old T-SQL function "RTRIM" or the newest one in SQL Server 2017, just called "TRIM".

Here is how it works. Just run following script:
DECLARE @Test VARCHAR(100) = 'ABC';
SET @Test += REPLICATE(' ',10);
PRINT 'Here is an example of not trimmed string concatination: ' + CHAR(09) + '"' + @Test + 'DEF"';

PRINT 'Here is an example of trimmed string concatination: ' + CHAR(09) + CHAR(09)+ '"' + RTRIM(@Test) + 'DEF"';


However, sometimes Trimming does not work and that happens if we have some unwanted invisible characters at the end of the string. See that example:

DECLARE @Test VARCHAR(100) = 'ABC';
SET @Test += REPLICATE(' ',10) + CHAR(9);
PRINT 'Here is an example of not trimmed string concatination: ' + CHAR(09) + '"' + @Test + 'DEF"';

PRINT 'Here is an example of trimmed string concatination: ' + CHAR(09) + CHAR(09)+ '"' + RTRIM(@Test) + 'DEF"';
RTRIM function simply did nothing and we know why, because the very last character in a string was not a space, but hidden unwanted symbol.

In our case we already know what that symbol is, but in the real life we have to research it, and here is how we do this:

DECLARE @Test VARCHAR(100) = 'ABC';
SET @Test += REPLICATE(' ',10) + CHAR(9);

SELECT ASCII(RIGHT(@Test,1));

Now, when we know, who is a troublemaker we can simply replace unwanted symbol by an empty space, but in our case we can't do that. Symbol with the code 0x09 represents Tabulation and if we simply replace it we can loose all our "TABs" within our text. So, we have to trim that last character of a text if it is unwanted, and only then we can do TRIM operation:

DECLARE @Test VARCHAR(100) = 'ABC';
SET @Test += REPLICATE(' ',10) + CHAR(9);
SET @Test = IIF(ASCII(RIGHT(@Test,1)) != 9,@Test,LEFT(@Test,LEN(@Test)-1))
PRINT 'Here is an example of not trimmed string concatination: ' + CHAR(09) + '"' + @Test + 'DEF"';

PRINT 'Here is an example of trimmed string concatination: ' + CHAR(09) + CHAR(09)+ '"' + RTRIM(@Test) + 'DEF"';
We trimmed the very last character if it is ASCII code 0x09 and then "RTRIM" function perfectly worked.

I'm pretty sure you might hit a situation when there will be more then one different trailing unwanted invisible symbols in your string column. Depending on what those symbols are you might prefer to use "REPLACE" function like this:
SET @Test = REPLACE(@Test,CHAR(9),'');
But as I mentioned earlier, be careful and do not delete a wanted symbol by a mistake.