European Windows 2012 Hosting BLOG

BLOG about Windows 2012 Hosting and SQL 2012 Hosting - Dedicated to European Windows Hosting Customer

European SQL 2008 R2 Hosting - Amsterdam :: Generated LINQ to SQL, SQL Server Profiler, and Provide Final T-SQL Generated

clock July 5, 2013 07:42 by author Scott

If you’re interested in taking a look at the final T-SQL generated by your LINQ to SQL queries then you have a few options available:

  1. Utilise LINQPad which converts your C# LINQ database queries into T-SQL
  2. Execute an SQL Server Profiler trace against the database your application is querying while its running
  3. Understand how ADO.NET translates the LINQ to SQL expression tree into T-SQL so you know what it’s going to output

So first off I’ll provide a brief overview of SQL Server Profiler and what a “trace” is. Then I’ll show you how to configure and run a trace. Finally I’ll throw a few LINQ to SQL examples at a local SQL database and provide the final T-SQL generated.

SQL Server Profiler & Traces

SQL Server Profiler comes packaged with SQL Server so if you’re running SQL Server Management Studio chances are you’ll find a shortcut to this particular tool in amongst the SQL shortcuts, or via the Tools menu option in SQL Server Management Studio. Its primary aim is to analyse, and even replay SQL trace results, for the purpose of troubleshooting, diagnosis, benchmarking, monitoring, etc. As a developer you can imagine how helpful this can be when you want to identify bottlenecks, optimise your system during the testing and performing tuning phase, generate performance reports, etc.

Once you delve into examining SQL events you’ll start to notice that SQL generates a LOT of unique events (well over a hundred) and in order to help isolate the ones you’re looking for you have to create what’s called a trace. The trace defines which SQL events to capture as well as providing very handy filtering and output options.

The trace results are also returned in a tabular fashion which you can browse through within the tool or export for use elsewhere.

Creating a Trace

Start up SQL Server Profiler. The first screen you’ll see will hopefully look like this:

Click on “File” in the menu and select “New Trace…” then select the appropriate connection settings.

You’ll then be presented with a fresh “Trace Properties” window from where you’ll be able to configure your trace.

Leave everything as is on the “General” tab with the exception of the “Use the template:” option which you want to change to “Blank”. This essentially clears the events the trace is going to capture so that we can start from scratch and only add those we want. There are a lot of preconfigured templates but we want to avoid those for now.

Now click on the “Events Selection” tab and you’ll see an array of events all grouped accordingly. We want a specific event called “RPC:Completed” which is under the “Stored Procedures” category.

Select the checkbox to the left of “RPC:Completed”. This tells our trace that we want to capture all events that are fired when a remote procedure call completes it’s execution.

One small but useful tip is to organise how the data will be returned so click on the “Organize Columns…” button and reorder the columns so the “StartTime”, “Duration” and “TextData” are at the top. This means we’ll have our trace results returned in chronological order and each will tell us how long they took to execute in milliseconds and what T-SQL was executed in the first few columns. See below:

Click “OK” to save the column order then click “Run” to proceed.

Congratulations you just created a trace!

Reviewing the Trace for LINQ to SQL queries

Now that the trace is up and running we’ll start to see all stored procedure calls that are completed against any database associated with the connection you chose earlier.

If you fire up a Windows C# application that makes LINQ to SQL calls you’ll start to see them appearing in the trace output. What’s important to clarify at this point is that even if your LINQ to SQL queries aren’t calling a stored procedure the system stored procedure sp_executesql will be executing the queries, and that’s what the trace will be capturing.

See below for an example:

What I’ve selected is a simple LINQ to SQL insert into a table called “ExceptionLog”, and here’s the C# code that generated this call:

var exceptionLog = new ExceptionLog
{
    ExceptionType = exceptionType,
    Message = message,
    AddedDateTime = DateTime.UtcNow
}; 

using (var dataContext = GetDataContext())
{
    dataContext.ExceptionLogs.InsertOnSubmit(exceptionLog);
    dataContext.SubmitChanges();
}

You can use the Pause/Start buttons to halt the trace if need be so you don’t find SQL Server Profiler auto-scrolling to the bottom of the list every time a new procedure is completed.

Here’s another example of a simple LINQ to SQL select:

var query =
    (
        from el in dataContext.ExceptionLogs
        where el.AddedDateTime.Year == 2012
        orderby el.AddedDateTime ascending
        select new ExceptionLog
        {
            ExceptionLogId = el.ExceptionLogId,
            ExceptionType = el.ExceptionType,
            AddedDateTime = el.AddedDateTime
        }
    ).ToList();

And in the trace window we’ll see:

exec sp_executesql N'SELECT [t0].[ExceptionLogId], [t0].[ExceptionType], [t0].[AddedDateTime]
FROM [dbo].[ExceptionLog] AS [t0]
WHERE DATEPART(Year, [t0].[AddedDateTime]) = @p0
ORDER BY [t0].[AddedDateTime]',N'@p0 int',@p0=2012

You can see the exact SQL output. More information is displayed above as well so you can get an idea for what other data is associated with the trace; duration in milliseconds, DatabaseName, RowCounts etc. All very useful.

Here’s another example where I’ve employed the SQL IN operator to retrieve a collection of authorised users. The LINQ to SQL:

var authorisedUserRoleIds = new List
{
    1, // 1 = System Administrator
    2, // 2 = Regional Manager
    3 // 3 = County Manager
}; 

var query =
    (
        from u in dataContext.Users
        join ur in dataContext.UserRoles on u.UserId equals ur.UserId
        where authorisedUserRoleIds.Contains(ur.RoleId)
        orderby u.Surname ascending
        orderby u.FirstName ascending
        select new User
        {
            UserId = u.UserId,
            FirstName = u.FirstName,
            Surname = u.Surname,
            EmailAddress = u.EmailAddress
        }
    ).ToList();

And the resulting SQL:

exec sp_executesql N'SELECT [t0].[UserId], [t0].[FirstName], [t0].[Surname], [t0].[EmailAddress]
FROM [dbo].[User] AS [t0]
INNER JOIN [dbo].[UserRole] AS [t1] ON [t0].[UserId] = [t1].[UserId]
WHERE [t1].[RoleId] IN (@p0, @p1, @p2)
ORDER BY [t0].[FirstName], [t0].[Surname]',N'@p0 int,@p1 int,@p2 int',@p0=1,@p1=2,@p2=3

If you look closely you can see the JOIN, WHERE  clause and ORDER BY  just as you’d expect.

What Next?

This is really a starting point and there is a lot more that can be achieved with SQL Server Profiler further. Some examples being:

If you want to edit your running trace you’ll have to pause it, select the properties option to edit it then get it running again – “File > Properties”.

Another useful event under the SQL group is SP:StmtCompleted. SP:StmtCompleted returns every single statement completed whereas RPC:Completed only returns the last remote procedure executed. So if you called a stored procedure which contained five individual queries from your LINQ to SQL you’d see five SP:StmtCompleted events and one RPC:Completed.

Save the trace you’ve created as a template so that you can reuse it again – “File > Save as Template“. You can even save it into the list of templates that appears in “New Trace” window for easier access or share it amongst your development team.

Apply filters to the trace in the “Events Selection” tab. See the “Column Filters…” button. Useful if you want to identify queries taking longer than 10 milliseconds or just target a given database.

Look into the Database Engine Tuning Advisor tool and how it can analyse trace outputs to help create indexes to boost performance.



SQL Server 2008 Hosting :: Tabular Reports in SQL Server 2008 R2 Reporting Services

clock November 16, 2011 16:00 by author Scott

It is important to understand the reporting options available in SQL Server 2008 R2 to provide an appropriate report layout to meet the business requirements and needs of our end users. This post briefly discusses about different report layout types available and explains in detail the steps to create a Tabular Report. It also explains how to sorting and drilldown features to your report.

Different Available Report Layouts are

- Tabular Reports – these reports are defined using table data and organized in to rows and columns. The columns are typically fields in the table. Reporting detail rows can be grouped on various fields, each group can have header, footer , breaks and subtotals.


- Matrix Reports
– To summarize the date for analysis we can use the Matrix Reports. It is useful for viewing the aggregated values with two different hierarchies(example time and geography).

- List Reports
– List report consist of single rectangular area that repeats for every record or group value in the dataset.

- Chart Reports
– these reports provide a visual context for different kinds of data. Some times complex information can be analyzed with very little explanation.

- Composite Reports
– You can combine the reports that we discussed above for compelling the advanced reports.

Creating Tabular Reports

1. Open the Report Builder 3.0 , select the Insert Tab and click Table on the ribbon.

As we have not created a data set and data source, it will prompt you to add these objects. Use the DataSource Properties window to enter a name for the new data source as shown below





2. Enter the following query in query designer

   1: Select CalendarYear,SalesTerritoryRegion, SalesAmount
   2: FROM
   3:     FactResellerSales as F INNER JOIN DimDate as D ON
   4:     F.OrderDateKey = D.DateKey INNER JOIN DimSalesTerritory as ST
   5:     ON F.SalesTerritoryKey = ST.SalesTerritoryKey
   6:
     order by CalendarYear , SalesTerritoryRegion

I am using the AdventureWorksR2 database to create this report.



3. Defining Table Groups – You can drop the database fields to zones to define groups located at the bottom of the designer pane.



In this example we are grouping the rows of this table based on CalendarYear and SalesTerritoryRegion Fileds. To add the CalendarYear field as a group above the detail row, drag and drop this field from the Data Window.

4. Drag the required fields to report designer as shown in the following window. As SalesAmount field is a currency type value and it should be formatted to show the values. To do so right click on the SalesAmount textbox and then choose properties





5. To look at the report that we built so far, you can click the run button in Report builder. Now the report is rendered as shown below



6.  Switch back to the design view to add totals to the end of a group with same result. Right-click on the CalendarYear row, and choose  Insert Row—>Inside Group – Below



The new row will be added below the group values. Hover the mouse over the new cell below the SalesAmount field and click on the field list icon as shown below



7. To see the changes in report, click on the run button to preview the report



8. Adding Sorting Feature to report – Any group can be sorted by any order using combination of data fields and expressions. For Example to set the sort order for the SalesTerritoryRegion Group click the group name under Row groups and select group properties you will see the following window



You can see the preview result in the following window



Creating Drill Down Reports – All report items and groups have a visibility property that can be set either permanently or conditionally. Common use is to create drill-down reports , where headers are used expand and collapse. Typically (+) or (-) sign will be displayed next to the column header.

To create a drilldown in this example, select CalendarYear Group from the lower part of the designer and click on group properties option then you should be able to get the following dialogue box



Set the display option to hide and select relevant textbox for the CalendarYear group by checking the Display toggle option. Click Ok to save these property changes.



European SQL 2008 Hosting :: Tutorial - Report Builder 2.0 in SQL Server 2008

clock June 9, 2011 05:22 by author Scott

Report builder 2.0 is a report authoring tool that we can use to design and publish reports. We can specify the data source, what data to display on report and which layout you prefer to see the report. When you run the report, the report processor takes all the information you specified and retrieves the data and generates each page as you view it. This post explains step-by-step details of creating the report using Report Builder 2.0.

Download Report Builder 2.0
here. Report Builder was first introduced in SQL server 2005 and it is still available in report.

After Installing Report Builder you can launch it by Start->All Programs->Microsoft SQL Server Report Builder 2.0.



1. You’ll see the following screen after launching the Report Builder 2.0:



2. Visually there are number of differences when you compare with the SQL Server 2005 report builder.

- The office 2007 ribbon interface.
- On the left hand side of the designer you will see built-in fields,report parameters, images and data fields.
- To begin designing a report, you can select either Table or Matrix or Chart icons from designer interface as shown above.

3. This post using the AdventureWorksDW2008 sample database as data source. You can download the databases from
here.  

4. Select the data source for you report as follows:



5. After completing the data source connection configuration, then click next to design a query for your report.



Either you can select the data fields or views from your database or you can use editor to type your sql query for binding data.

6. Click next to get the Arrange fields-dialogue box where you can drag and drop your data fields into the Row groups.



7. Click Next to choose the layout for your report.



8. You can also the chart in your report with the same data source which we defined above as follows



9. Run the above report then you will see the following output



10. You can deploy the above designed report to a SQL server 2008 Report Server. You should specify the URL of the default report server in Report Builder Settings. Click the database icon in top-left corner from Report-Builder window and you will see the following dialogue



You can enter the URL of your default report server or SharePoint site where you want to deploy your reports.



European SQL 2008 R2 Hosting :: 10 Tips for Upgrading to SQL Server 2008 R2

clock May 5, 2011 08:19 by author Scott

Upgrading a database server to SQL Server 2008 R2 involves more than just inserting an installation DVD and clicking your way through the wizard. A lot of planning goes into a SQL Server upgrade. In this article, I will share with you 10 tips that should help your upgrade process to go more smoothly. If you're looking for Windows hosting that support SQL 2008 R2, you may try us to be your partner. Please take a look our hosting plan at here. OK, let we start this article..

1. Be aware of the supported upgrade paths

Before you begin planning an upgrade, you need to be aware of Microsoft’s supported upgrade paths. For example, if you are currently running SQL Server 2005 X64 Enterprise Edition, you can’t upgrade to SQL Server 2008 R2 Standard Edition. Your only options are to upgrade to SQL Server 2008 R2 Enterprise Edition or Datacenter Edition.

2. Run the Upgrade Advisor

The Upgrade Advisor, which is a part of the
SQL Server 2008 R2 Feature Pack, is a free utility that is designed to assist you with your SQL Server 2008 R2 upgrade. The tool analyzes your existing SQL Server
deployment and informs you of issues that need to be addressed prior to performing an upgrade.

3. Don’t panic over Other Issues

The report generated by the SQL Server Upgrade Advisor often contains a section called Other Issues. This section exists as a way of informing you of possible issues that may exist, but that the tool is incapable of testing. Therefore, the issues that appear in the Other Issues section may not necessarily be present on your network.

4. Figure out what to do about the Notification Services

If you have SQL Servers that are running the Notification Services, you will need to plan how you want to deal with them. The Notification
Services were discontinued starting with SQL Server 2008, and can’t be upgraded to SQL Server 2008 R2.

5. Verify the hardware and software requirements

If you’re considering an in-place upgrade (rather than a migration), it is critically important to verify that your existing SQL Server meets all the hardware requirements for running SQL Server 2008 R2 and that all the necessary software prerequisites are in place. Check out this full list of the hardware and software requirements.

6. Perform a full backup

Although it should go without saying, you should always perform a full server backup prior to performing a SQL Server 2008 R2 upgrade. The upgrade process usually goes smoothly, but things can and sometimes do go wrong. It’s important to have a way to revert your SQL Server to its previous state if the upgrade does not go as planned.

7. Take care when upgrading the database engine

There are a few things that you should do prior to upgrading the database engine to ensure that things go smoothly. First, if you are running the Analysis Services, make sure you upgrade them before you upgrade the database engine. The Analysis Services must be upgraded first.

Just before the upgrade, temporarily disable any stored procedures. During the course of the upgrade, various SQL-related services will be started and stopped. If you have stored procedures that are configured to run when services start, there is a good chance those stored procedures will interfere with the upgrade.

Also check the Master, Model, MSDB, and TEMPDB databases and verify that they’re set to autogrow (and that there is plenty of disk space available). In addition, be sure to disable database replication prior to performing an upgrade.

Finally, even though SQL Server 2008 R2 is designed to preserve the Max Worker Threads setting, Microsoft recommends setting the Max Worker Threads value to 0. This will cause SQL Server 2008 R2 to automatically calculate the optimal value.

8. Be aware of discontinued features

Microsoft has removed the Surface Area Configuration Tool from SQL Server 2008 R2. Most of the tool’s functionality still exists, but it has been rolled into other areas of the application. For example, protocols, connection, and startup options are now found in the SQL Server Configuration Manager. If you use the Surface Area Configuration Tool from time to time, it’s a good idea to deploy SQL Server 2008 R2 in a lab environment so that you can get a feel for what it takes to manage SQL without this tool.

9. Perform a test upgrade

Before you attempt to upgrade a production database server, try the upgrade in a lab environment. Make a full backup of a domain controller, a DNS server, your SQL server, and any other required infrastructure servers and then restore those backups to isolated lab servers. Once SQL is up and running, try out your upgrade plan in the lab. That way, you can handle any issues that come up before you have to perform a real upgrade.

10. Don’t forget to clean up when you’re finished

When the upgrade is complete, run DBCC UPDATEUSAGE on all of your databases to ensure database integrity. You will also need to reregister your servers and repopulate full text catalogs. If you have disabled replication or disabled stored procedures for the upgrade, you will need to put things back to normal.



About HostForLIFE.eu

HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

We have offered the latest Windows 2016 Hosting, ASP.NET Core 2.2.1 Hosting, ASP.NET MVC 6 Hosting and SQL 2017 Hosting.


Tag cloud

Sign in