Monday, April 27, 2015

Doing SQL Saturday in Baltimore



There is almost nothing about SQL Server in that post, other than just about brand new - first time "SQL Saturday in Baltimore".


FAQ:

What is SQL Saturday?
- SQL Saturday is FREE full day learning event about SQL server technologies and around.

What "BI Edition" means?
- We decided to make our first SQL Saturday event dedicated to "Business Intelligence". That means we give more time not for SQL Server engine, administering or SQL programming, but to SSIS, SSAS and SSRS technologies. We also include different types of reporting, such as: SharePoint and Office 365.

Why do I have to attend SQL Saturday in Baltimore?
Here are cases when you should go:
- If you are working with SQL Server as DBA or Developer;
- If you are Java/VB/.NET or any other developer who uses Database Server on the back end;
- If you are Business Analyst, whose job is to analyze the data and make conclusions and predictions;
- If you are Team Lead or Project Manager, whose team is using any database technologies;
- If you are looking in a new career and want to learn about new technologies;
- If you are a student and want to work in IT;
- Finally, if you want to be on the top of the edge of technology and want your head above  of all your peers.



If you are interested in participating follow the link and click "Register": http://www.sqlsaturday.com/395/eventhome.aspx

Tuesday, March 24, 2015

SQL Monitoring Tools - Idera

That post will start the series of SQL Monitoring Tools' reviews. Product installations and overall experience.

Will start with Idera (V.9.0.0.1189 x64).
Why "Idera"? - because that company is almost always sponsoring SQL Saturday events, which help to develop local SQL talents.


At first, I've looked at Idera.com and found "SQL Diagnostic Manager".
In order to download I had to register and provide my email and phone number.

When I click to download ZIP file the page was loading for several minutes before the actual download started.

After extracting Executable file and starting it I was asked for another place to unpack. That seems not very convenient, everything could be packed into the ZIP file, now I have two copies of installation.

When installation started and I got following window I was little bit confused what do I have to do:
After I've figured out which option to choose I've got the window that I couldn't comprehend:
In my assumption here I have to specify parameters of my workstation.
Two big questions are not answered: Why do I have to create "Idera_Service_Account" with no rights and what port 9292 stands for?

After creating dummy "Idera_Service_Account" and specifying in the form, I was asked to accept the License Agreement.
Then I provided installation path and hit another bump:
The installer just asked me for a service account a minute ago.
Can I use the same account or it should be different?
Why it can't run under current system account? HELP!!!!

Obviously, the same account did not work. I had to put my domain account.
Can anybody guarantee that info was not sent immediately to hackers or not stored locally in the easy to break format?

Mystery of port 9292 is solved:


Have a bump again:

After Specifying the SQL Server instance Installer was satisfied with previously entered credentials.
Two more screens/clicks and installation has started and finished almost immediately.

What do I have to do next?
Search in Start Menu brought me hidden "Idera Dashboard", which was actually a link to following location: https://localhost:9291

Now I have to provide my credentials again...
 After logging in I've finally got to "Idera Dashboard". Is it the one?
In order to try Idera SQL Monitoring I went to "Manage Instances" tab:
I did not expect to see anything in the list, but I couldn't locate "ADD" button anywhere.

However I've found "Help" button.
Search on "Add SQL Server Instance" lead me to only one page: "http://wiki.idera.com/pages/viewpage.action?pageId=38339658" - Why?

Then I realized that there are two search options and only one of them really works:


Finally I've found how to Register new SQL Server:
Right here I've stopped. Help document states that I have to do something obvious, which does not exist in the menu.

Almost gave up when found hidden installation window. It looks like installation is not finished yet.

That installer looks differently, it asked me to accept license agreement again. Why?

It looks like  previous install was "Idera Dashboard" installation and that one is actual "Diagnostic Manager":

After couple of screens I had to provide my local server name again:

But live is not so easy. While Idera Dashboard easily found my server "Diagnostic Manager" gave me following message:

Very nice. After I Enabled "TCP/IP" protocol it recognized my server and asked for my credentials again. I could use SQL server Authentication, but was too lazy to create a new account.

It looks like it was the last challenge.The fact that two installers do not pass data to each other and connect to servers.

Finally got to "Diagnostic Manager" screen.
Added new server. After all previous stupidity it was pretty straight.

At start, "Diagnostic Manager Console" immediately scared me by red crosses:

 I've tried following simple query:
SELECT
    physical_memory_kb/1024 AS [Physical Memory (MB)],
    committed_target_kb/1024 AS [Committed Target Memory (MB)]
FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE);

It returned me the following result:
That means Server Memory is not allocated 100% to the SQL Server.
However, it is possible that other processes eat the rest of the memory and server is paging to the disc.
I've logged to the server and checked current state of the memory:
Obviously, we still have 9 Gb of free memory.
What 100% "Diagnostic Manager Console" refers to?

Looking at "Memory branch" to discover the problem:

There is no case that 100% of memory is used. As you can see, SQL Server is actually using just little over 60% of all allocated memory. Page Life Expectancy is huge. Everything looks healthy.



Now I'm trying to discover tool's functionality.
That is a real time view of SQL connections:

Real time view of system resources:

I also could go through all databases' configurations, their sizes, file allocations, tables' and indexes' sizes, backups and mirroring.
Tool allows to rebuild indexes and update statistics.

Next area is Services: Agent, Full-Text Search and Replication. There is "Critical Error" warning sign because the monitor can't access to the server for service monitoring. Who said it should?

And the last one is logging area for SQL Server and Agent logs.

Here is the full tree of available metrics:
As part of my evaluation I've tried to chat with help desk regarding "100% Memory usage" alert.
Unfortunately, the guy at the help desk couldn't help because all evaluations are serviced only by sales personal. So, I have to use email or phone and that would be "advertizement service", not the real one  I was trying to evaluate.

At the end I want to measure my experience for the product on the scale of 10:

Installation 5 (because of 5 problems and reasonable questions during the process)
Web interface (Dashboard): 1 (can't use it at all)
Diagnostic Manager: 8 (false memory alert and inability to acces to services alert)

General impression: Idera Diagnostic Manager provides pretty accurate real time monitoring of Key SQL components, but it is not very usable to do monitoring from the historical perspective.
That is "nice to have" tool when you constantly dealing with problems, but in stable environment you can use it only as alerting tool.

















Monday, February 23, 2015

Drawing a Hyperbola in SSMS

<= Drawing a Parabola in SSMS


The last and most common shape of motion in the space is Hyperbola.

Small objects from outer space change their orbits insignificantly under heavy gravitation of bigger bodies.

Here is the formula I've used to draw hyperbolas:
Where:
X and Y - Hyperbola's coordinates;
A and B - imaginary central point;
L - slop.


Here is a classic hyperbola script for SQL Server:

DECLARE @MP VARCHAR(MAX)='';

DECLARE @l DECIMAL(8,4) = 1;    -- Slope
DECLARE @bx DECIMAL(8,4) = 0;    -- X-Center point
DECLARE @by DECIMAL(8,4) = 0;    -- Y-Center point
DECLARE @s DECIMAL(8,4) = 10;    -- X-Span of Hyperbola
DECLARE @Step DECIMAL(8,4) = 0.1;    -- Step
DECLARE @k DECIMAL(8,4) = 0;

DECLARE @cx DECIMAL(8,4) = -@s;    -- X-Current point
DECLARE @x1 DECIMAL(19,9);
DECLARE @y1 DECIMAL(19,9);
DECLARE @x2 DECIMAL(19,9) = @cx + @bx;
DECLARE @y2 DECIMAL(19,9) = @by + @k*@x2 + @l / (@x2 - @bx);

WHILE @cx < @s
SELECT
    @cx += @Step,
    @x1 = @x2, @y1 = @y2,
    @x2 = @cx + @bx,
    @y2 = CASE @cx WHEN 0 THEN @y1
                ELSE @by + @k*@x2 + @l / (@x2 - @bx) END,
    @MP = @MP + CASE WHEN @x2 = @bx  
                OR (@x1 <= @bx and @x2 >= @bx ) THEN '' ELSE '('
        + CAST(@x1 as VARCHAR) +  ' '
        + CAST(@y1 as VARCHAR) + ','
        + CAST(@x2 as VARCHAR) +  ' '
        + CAST(@y2 as VARCHAR) + '),' END ;

SELECT CAST('MULTILINESTRING(' + LEFT(@MP,LEN(@MP)-1) + ')' as geometry);

Here is the result of that script in SSMS:

By adjusting variables you can get variety of hyperbola shapes:

DECLARE @l DECIMAL(8,4) = -2;    -- Slope
DECLARE @bx DECIMAL(8,4) = 12;    -- X-Center point
DECLARE @by DECIMAL(8,4) = 3;    -- Y-Center point
DECLARE @s DECIMAL(8,4) = 25;    -- X-Span of Hyperbola
DECLARE @Step DECIMAL(8,4) = 0.1;    -- Step
DECLARE @k DECIMAL(8,4) = 0.5;

Sunday, February 22, 2015

Drawing a Parabola in SSMS

<= Drawing an Ellipse in SSMS

Drawing a Hyperbola in SSMS =>


Some asteroids are first and last time visitors in our Solar System.
They travel between stars in Milky Way galaxy and sometimes come so close to our Sun that completely change their orbit.
Usually their orbit is changed in shape of hyperbola, but sometimes they are going by parabola shaped orbit.

The formula I've used is not a conventional one, but it served my needs pretty well:
Where:
X and Y - parabola's coordinates;
A and B - coordinates of parabola's vertex;
L -  parabola's slop;
n - parabola's power;


So, it is a time will draw a Parabola in SQL Server management studio.

Here is a script to draw classical shaped parabola:
DECLARE @MP VARCHAR(MAX)='';

DECLARE @l DECIMAL(8,4) = 1;    -- Slope
DECLARE @bx DECIMAL(8,4) = 0;    -- X-bottom point
DECLARE @by DECIMAL(8,4) = 0;    -- Y-bottom point
DECLARE @p DECIMAL(8,4) = 2;    -- Parabola's Power
DECLARE @Step DECIMAL(8,4) = 0.01;    -- Step
DECLARE @s DECIMAL(8,4) = 5;    -- X-Span of Parabola

DECLARE @cx DECIMAL(8,4) = -@s;    -- X-Current point
DECLARE @x1 DECIMAL(19,4);
DECLARE @y1 DECIMAL(19,4);
DECLARE @x2 DECIMAL(19,4) = @cx + @bx;
DECLARE @y2 DECIMAL(19,4) = @by + @l * POWER(ABS(@x2 - @bx),@p);

WHILE @cx < @s
SELECT
    @cx += @Step,
    @x1 = @x2, @y1 = @y2,
    @x2 = @cx + @bx,
    @y2 = @by + @l * POWER(ABS(@x2 - @bx),@p),
    @MP = @MP + '('
        + CAST(@x1 as VARCHAR) +  ' '
        + CAST(@y1 as VARCHAR) + ','
        + CAST(@x2 as VARCHAR) +  ' '
        + CAST(@y2 as VARCHAR) + '),';

SELECT CAST('MULTILINESTRING(' + LEFT(@MP,LEN(@MP)-1) + ')' as geometry);

As the result I've got that beautiful picture:

By changing slope, parabola power and the coordinates of the bottom point you can get very different parabola shapes.

Here is one example of parameter set:
DECLARE @l DECIMAL(8,4) = -0.5;    -- Slope
DECLARE @bx DECIMAL(8,4) = 7;    -- X-bottom point
DECLARE @by DECIMAL(8,4) = 3;    -- Y-bottom point
DECLARE @p DECIMAL(8,4) = 1.5;    -- Parabola's Power


In this case parabola's bottom point has coordinates (7,3), it has a negative slope and even its power not a whole number.

Try to reshape it by changing variables.

Saturday, February 21, 2015

Drawing an Ellipse in SSMS


<= Building Sky Map in SSMS


Drawing a Parabola in SSMS =>



I was looking at orbits of planets, comets and asteroids and questioned myself if I can reproduce their tracks in SQL Server.

The first try is drawing Ellipse:

DECLARE @MP VARCHAR(MAX)='';

DECLARE @theta DECIMAL(8,4) = 0;-- angle that will be increased each loop
DECLARE @h DECIMAL(8,4) = 0;    -- x coordinate of center
DECLARE @k DECIMAL(8,4) = 0;    -- y coordinate of center
DECLARE @step DECIMAL(8,4) = 2 * PI()/100;  -- amount to add to theta each time (degrees)
DECLARE @a DECIMAL(8,4) = 10;    -- major axis
DECLARE @b DECIMAL(8,4) = 5;    -- minor axis

DECLARE @x1 DECIMAL(8,4), @y1 DECIMAL(8,4);
DECLARE @x2 DECIMAL(8,4) = @h + @a * COS(@theta);
DECLARE @y2 DECIMAL(8,4) = @k + @b * SIN(@theta);

WHILE @theta <= 2 * PI()
SELECT
    @x1 = @x2, @y1 = @y2,
    @x2 = @h + @a * COS(@theta),
    @y2 = @k + @b * SIN(@theta),
    @theta += @step,
    @MP = @MP + '('
        + CAST(@x1 as VARCHAR) +  ' '
        + CAST(@y1 as VARCHAR) + ','
        + CAST(@x2 as VARCHAR) +  ' '
        + CAST(@y2 as VARCHAR) + '),';

SELECT CAST('MULTILINESTRING(' + LEFT(@MP,LEN(@MP)-1) + ')' as geometry);

In this script you can change following parameters:
- Coordinates of the center of an ellipse - @h & @k;
- Lengths of Major and Minor axes' - @a & @b;
- Size of one step incremental angle - @step. That parameter also controls number of steps.

With a step size - 1/100 of a circle and axis' sizes 10 and 5 I've got that pretty Ellipse:


Friday, February 20, 2015

Catch 22 or creating SQL dependency circle.

That is a kind of a SQL joke. Part of the fun being a DBA.

First case of SQL dependency circle I will demonstrate on two tables interdependent on each other.

Here is a script to create these simple tables and build dependency relationships to each other:
USE TestDB;
GO
CREATE TABLE tbl_Catch_1(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Catch_2 INT
);
GO
CREATE TABLE tbl_Catch_2(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Catch_1 INT
);
GO
ALTER TABLE tbl_Catch_1 ADD CONSTRAINT FK_Catch_1
FOREIGN KEY (Catch_2) REFERENCES tbl_Catch_2(ID);
GO
ALTER TABLE tbl_Catch_2 ADD CONSTRAINT FK_Catch_2
FOREIGN KEY (Catch_1) REFERENCES tbl_Catch_1(ID);
GO

Now, if you try to delete any of these tables you will get an error kind of:
Msg 3726, Level 16, State 1, Line 78
Could not drop object 'tbl_Catch_1' because it is referenced by a FOREIGN KEY constraint.
Msg 3726, Level 16, State 1, Line 80
Could not drop object 'tbl_Catch_2' because it is referenced by a FOREIGN KEY constraint.

It is very simple to resolve that issue by dropping one of the relationships first:
ALTER TABLE tbl_Catch_1 DROP CONSTRAINT FK_Catch_1;
GO
DROP TABLE tbl_Catch_2;
GO
DROP TABLE tbl_Catch_1;
GO


The second SQL interdependency example will be little bit more complicated:

Here I create an interdependency between table an a function:
USE TestDB;
GO
CREATE TABLE tbl_Catch_3(ID INT IDENTITY(1,1));
GO
CREATE FUNCTION
dbo.fn_Catch_3()
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
  RETURN
(SELECT MAX(ID) + 1 FROM dbo.tbl_Catch_3);
END;
GO
ALTER TABLE
tbl_Catch_3 ADD Next_ID as dbo.fn_Catch_3();
GO

Try to see if table and function are functioning correctly:
BEGIN
    INSERT INTO
tbl_Catch_3 DEFAULT VALUES;
    SELECT * FROM tbl_Catch_3
    WHERE ID = (SELECT MAX(ID) FROM tbl_Catch_3);
END
GO 5
GO
Everything looks fine:

Now, if we try to delete the table or the function we get following messages:
Msg 3729, Level 16, State 1, Line 117
Cannot DROP FUNCTION 'dbo.fn_Catch_3' because it is being referenced by object 'tbl_Catch_3'.
Msg 3729, Level 16, State 1, Line 119
Cannot DROP TABLE 'tbl_Catch_3' because it is being referenced by object 'fn_Catch_3'.

The easiest way to resolve that issue is to go backwards and delete computed column first:
ALTER TABLE tbl_Catch_3 DROP COLUMN Next_ID;
GO
DROP FUNCTION dbo.fn_Catch_3;
GO
DROP TABLE tbl_Catch_1;
GO

I'm pretty sure there are many other ways to have SQL dependency circle, but these two are only I have faced.

Thursday, February 19, 2015

Collation on a single column

You specify collation when you install SQL Server.

What is "COLLATION"? - That is the way how SQL Server interprets ASCII symbols and character codes. It is applicably not only for "English", but for any supported Unicode languages.

Most of the time the default collation always works and nobody even look at it. All new databases will have the same collation and all CHAR/NCHAR/VARCHAR/NCHAR fields.

However, sometimes you have to pay attention to your collation.
There might be a case when you attach database from another server with different collation.
Sometimes your business rules dictate that values have to be case sensitive and should be sorted in certain order. Then you have to change your collation.

At first, how do you know what is default collation on your SQL Server?
Run following command:
SELECT SERVERPROPERTY(N'Collation');
My SQL Express returns "SQL_Latin1_General_CP1_CI_AS" - the regular default value.

If you want, you can change collation for any database.
Following script demonstrates different results of the same query with different database collation and two different ways to retrieve default collation information for a database:
USE [TestDB];
GO 
SELECT collation_name FROM sys.databases WHERE Name = 'TestDB'; GO
SELECT 'A','A' UNION 
SELECT 'a','a' UNION
SELECT 'B','B' UNION
SELECT 'b','b';
GO 

ALTER DATABASE [TestDB] COLLATE SQL_Latin1_General_CP1_CS_AS;
GO
SELECT DATABASEPROPERTYEX('TestDB', 'Collation') as collation_name;

GO
SELECT 'A','A' UNION 
SELECT 'a','a' UNION
SELECT 'B','B' UNION
SELECT 'b','b';
GO 
 When we have default case insensitive collation UNION statement collapses upper and lover case values, but when we switch database to case sensitive collation query returned all four rows.

Here is a demonstration how collation effects a table:
USE [TestDB];
GO 
CREATE TABLE tbl_test_Collation(
    A CHAR(1),
    B CHAR(1)
);
GO 
INSERT INTO tbl_test_Collation(A, B) 
SELECT 'A','A' UNION 
SELECT 'a','a' UNION
SELECT 'B','B' UNION
SELECT 'b','b';
GO
SELECT Name, collation_name FROM sys.columns
WHERE object_id = OBJECT_ID('tbl_test_Collation');

GO
As you can see, both fields in the table have default collation for the database:

The beauty of SQL Server collation is in ability to set individual collation on every column:

ALTER TABLE tbl_test_Collation  
ALTER COLUMN A CHAR(1) COLLATE SQL_Latin1_General_CP1_CI_AS;
GO
ALTER TABLE
tbl_test_Collation
 
ALTER COLUMN B CHAR(1) COLLATE Latin1_General_BIN;
GO
SELECT
Name, collation_name FROM
sys.columns
WHERE object_id = OBJECT_ID('tbl_test_Collation'); 
GO
As a result we have two columns in a table with different collation:

Now will play around with that table. As you noticed, values in column "A" and "B" are identical:
SELECT * FROM tbl_test_Collation WHERE A = 'a';
GO
 
SELECT * FROM tbl_test_Collation WHERE B = 'a';
GO

We can see that first query, when we filtered by case insensitive column, returned two records and filter on case sensitive column returned only one record.
Ordering, using different collation, is even more interesting:
SELECT * FROM tbl_test_Collation ORDER BY A; 
GO
SELECT * FROM tbl_test_Collation ORDER BY B; 
GO
First and the second queries have completely different order:
      
Ordering by case insensitive column sorts in alphabetical order. Ordering by "Binary sort" collation sorts by characters' ASCII codes.

Sometimes we need to JOIN, Link or concatenate character fields with different collation.
Here are couple of examples which will generate errors:

SELECT A + B FROM tbl_test_Collation;
GO
SELECT * FROM tbl_test_Collation WHERE A = B;GO

Here is the way to fix these queries:
SELECT A + B COLLATE SQL_Latin1_General_CP1_CI_AS  
FROM tbl_test_Collation;GO
SELECT * FROM tbl_test_Collation 
WHERE A COLLATE Latin1_General_BIN = B;GO

However, be very careful joining columns, which do not have the same collation.
For example two queries below looks exactly the same, but return different sets of records:
SELECT * FROM tbl_test_Collation as a
INNER JOIN tbl_test_Collation
as b
    ON a.A
COLLATE Latin1_General_BIN = b.B;
GO
SELECT * FROM tbl_test_Collation as a INNER JOIN tbl_test_Collation as b
   
ON a.A = b.B COLLATE SQL_Latin1_General_CP1_CI_AS;GO

To prevent discrepancy between queries you can use "DATABASE_DEFAULT" option instead of specifying particular collation:
SELECT * FROM tbl_test_Collation as a INNER JOIN tbl_test_Collation as b
   
ON a.A COLLATE DATABASE_DEFAULT = b.B COLLATE DATABASE_DEFAULT;GO

I want to finish this post by the simple query to retrieve list of all available collations.
In SQL Server 2014 is almost 4K collations. To see only applicable to your language you can use following script
SELECT name, [description] FROM sys.fn_helpcollations()
WHERE name like 'Greek%';