Improving the performance by using 'Boost SQL Server Priority' option

Boost SQL Server Priority is one of the many options that we can configure at SQL Server instance level in order to improve the performance of the database engine by changing its priority running on Microsoft Windows Server.

By default, the value of this option is 0 (zero) which says to Windows Server to execute SQL Server under normal priority either running on one processor or symmetric multiprocessor environment. On the other hand, if we configure this value to 1, SQL Server will be executed with a major priority which might improve the performance of a database server. This option works very well on servers which are only dedicated to SQL Server. In contrast, it is not highly recommended using it when there are more applications running on the same server.

'Boost SQL Server Priority' option can be configured via either SQL Management Studio or T-SQL. In this post we are going to see how to configure it via the second way. Because this option is an Advanced Option, we firstly need to enable the possibility of accessing to it, which means that we have to enable 'show advanced options' option:

sys.sp_configure 'show advanced options', 1

Shortly after that, we must confirm the change without restating the database engine:

RECONFIGURE

GO

Having done that, we move on configuring 'Boost SQL Server Priority'.

EXEC sys.sp_configure N'priority boost', N'1'

GO

Now we need to commit and override the change.

RECONFIGURE WITH OVERRIDE

GO

That is all we need to do in order to enable this option. It couldn't have been simpler.
To sum up, at this point I need to ask you to work with a lot of cautiousness as this configuration might impact on the whole database engine performance. If there were many SQL Server database engines running on the same server and this option was enabled only for some of them, the rest of them might be impacted detrimentally. Nevertheless, I personally do not recommend enabling it on machines running on only one processor or many database engines (or other applications). Thanks for reading. Stay tuned.

Exploring ROW_NUMBER function in SQL Server 2005

Some of the SQL Server 2005 new features are the Ranking functions which are used to analyse data. Among these functions we have, for instance, ROW_NUMBER which helps to enumerate every row of a result set in a simpler way. Before showing the illustration of it, I will share the syntax:

ROW_NUMBER ( ) OVER ( [ partition_by_clause ] order_by_clause )
where:

• partition_by_clause: Divide the result set generated by FROM clause into partitions on which ROW_NUMBER will work.
• order_by_clause: Determine the order for ROW_NUMBER.

Now the illustration: we are going to enumerate the details of the sales based on the price.

SELECT ROW_NUMBER() OVER( ORDER BY UnitPrice DESC ) AS Item, 
SaleOrderID, ProductID, Quantity,UnitPrice 
FROM OrderDetail 

Item    SaleOrderID     ProductID   Quantity    UnitPrice
----    ------------    ---------   --------    -----------
1        00000015       FX8S           1        7621.71
2        00000016       FX8E           1        3601.77
3        00000016       FX5P           1        2207.87
4        00000014       FX5G           1        6507.82
5        00000018       FX21           1        631.62
6        00000019       FX19           1        751.62

Another example of it would be using PARTITION clause for partitions result sets by ProductGroupID and then ROW_NUMBER will be applied based on ProductStock. Therefore, we are writing a query like this:

SELECT ROW_NUMBER() OVER(PARTITION BY ProductGroupID ORDER BY ProductStock ASC) AS Item, 
ProductID, ProductDesc, ProductGroupID, ProductStock 
FROM Products

Item  ProductID    ProductDesc                       ProductGroupID    ProductStock

----  ---------    ------------------------------    --------------    -----------
1     DS02         MAINBOARD 845 PEMYL               G003               -1
2     DS06         MAINBOARD 850 EV2                 G003               10
1     DS07         TECLADO 104 MULTIMEDIA32          G004               20
1     DS12         SWITCH 5 PORT 10/100, 220V        G006               26
1     DS45         MUEBLE DE OFICINA                 G007               34
1     DS76         DISCO DURO                        G008               20
1     DS07         IMPRESORA LASER                   G009               10
1     DS45         MEMORIA STICK                     G010               2
2     DS29         MEMORIA VIDEO                     G010               5
1     DS08         TINTA CANON BCI24 NEGRO           G011               12
1     DS41         WEB CAM ALTIOR MODELO B02         G012               0
1     DS74         MONITOR DE 15                     G016               6
1     DS83         IMPRESORA MATRICIAL DFX5001       G018               12
2     DS84         IMPRESORA MATRICIAL DFX8502       G018               17
3     DS21         IMPRESORA MATRICIAL FX21944       G018               48

(15 row(s) affected)

As I mentioned before, this functionality is helpful, simple and easy to use it, so it does not worth a major explanation. That is all for now. Thanks for reading. Stay tuned.

The power of PIVOT in SQL Server 2005

With the arrival of SQL Server 2005, we have many new features available to make the most out of it. Today's post is going to be about one of the greatest new functionalities, that is, the powerful PIVOT sentence which can be used for converting rows into columns (and vice versa) in order to have a vision much more structured and meaningful of the data result sets.

So, moving on the example, we are going to use some tables of the 'Northwind' database like 'Orders', 'Orders Details', 'Employees', and 'Products'. The objective is to show the total value sold by each employee for the following products: 'Alice Mutton', 'Filo Mix', 'Flotemysost', 'Geitost', 'Konbu', 'Maxilaku', 'Pavlova', 'Tofu', and  'Vegie-spread'. Well, in SQL Server 2000, it can be done by using INNER JOINs and 3 Subqueries (if you had more tables to query, it would become a complex work to do). As a result of this, not only does the query become complex, but also it is not the most optimum choice.

SELECT Ventas1.*

FROM (SELECT Empleado, 

        (SELECT Products.ProductName FROM Products    

          WHERE Products.Productid=Ventas.ProductID) Producto, 

                SUM (ventas.ValorVendido)Total

      FROM (SELECT (C.lastname + ‘ ‘ + c.firstname)Empleado,ProductId,

                 (OD.Unitprice*OD.Quantity) [ValorVendido]

            FROM [Order Details] OD

             INNER JOIN ( [Orders] O        

              INNER JOIN Employees C

            ON O.Employeeid=C.Employeeid)

             ON OD.Orderid=O.OrderID) Ventas

GROUP BY Empleado, Productid) Ventas1

WHERE Producto IN(‘Alice Mutton’,‘Filo Mix’,‘Flotemysost’,‘Geitost’,

‘Konbu’,‘Maxilaku’,‘Pavlova’,‘Tofu’,‘Vegie-spread’)

Here we can see the partial result:



Having seen how difficult some queries can become by using old-fashioned and traditional techniques, now we are going rewrite the query to use PIVOT. Here is the code:

SELECT Empleado, [Alice Mutton],[Filo Mix],[Flotemysost],

      [Geitost],[Konbu], [Maxilaku],[Pavlova],[Tofu],[Vegie-spread]

FROM (SELECT Empleado, 

             (SELECT PRoducts.Productname FROM Products

             WHERE Products.Productid=Ventas.Productid) Producto,

                 SUM (ventas.ValorVendido) Total

      FROM (SELECT (C.lastname+ ‘ ‘ + C.firstname) Empleado, ProductID, 

               (OD.Unitprice*OD.Quantity) [ValorVendido]

            FROM [Order Details]OD

                INNER JOIN ([Orders] O

                INNER JOIN Employees C

             ON O.EmployeeID=C.EmployeeID

        )

ON OD.OrderID=O.OrderID) Ventas

GROUP BY Empleado,Productid) ventas1

PIVOT(SUM(total) FOR [Producto] IN ([Alice Mutton],[Filo Mix],[Flotemysost],

[Geitost],[Konbu],[Maxilaku],[Pavlova],[Tofu],[Vegie-spread])) AS pvt

Only after successfully familiarising with PIVOT will we realise how easy and practical is use it so as to save not only time but also costs. More details about it can be found by checking the following resource http://msdn2.microsoft.com/es-es/library/ms177410.aspx.

That is all for now until next post. Let me know any remarks and experience you have may using PIVOT. Thanks for reading. Stay tuned.

How to connect to SQL Server 2005 much faster via SQL Management Studio

While connecting with SQL Server 2005, we have to deal with the 'splash' screen and then indicate, basically, the following information such as service type, server name, and authentication type. This process may sometimes slow us down, nevertheless, we can optimise it as long as we know where we want to establish a connection. Put differently, we can save some time by using 'sqlwb.exe' tool which allows to start SQL Management Studio rapidly. This tool has some parameters we can use them to speed up the logon process to SQL instances. Here I am coming up with an illustration how to use 'sqlwb.exe' with some basic parameters. For instance,  if we would like to connect to the 'TestDB' database on 'SVPRDB1' server and with Windows Authentication, it can be done in this way:

sqlwb.exe -E -S SVPRDB1 -d TestDB -nosplash

Only after successfully executing that will we connect directly to 'TestDB' database without specifying manually the parameters and also without seeing 'the splash' screen showing the presentation of SQL Management Studio version. Now we can also customise the shortcut as it is shown in the following picture:



As earlier I said, this is the fastest way to connect to SQL Server which will allow to save some seconds. It couldn't have been simpler. That is all for now. Thanks for reading. Stay tuned.