Archive

Archive for May, 2011

SQL Nexus–How to import Performance Data collected using PSSDiag ?–Part 2

May 31, 2011 4 comments

In the last post, we saw how to install, configure and collect performance data from SQL Server 2005/2008/2008 R2, in this post, we will how to import that data using SQL Nexus and get ready to prepare Performance Analysis Reports

SQLNexus is a free tool developed by SQL Server support team and used for importing and analyzing performance data collected using SQL Diag and PSSDiag. Its helps identifying the Bottle necks, blocks, CPU consuming queries, Wait, Locks information, etc…

SQLNexus is freely available for download from http://sqlnexus.codeplex.com/

To run SQLNexus, you need to install the following prerequisites

.Net Framework 3.5

SQL Server 2008 SP1 Report viewer control (http://www.microsoft.com/downloads/details.aspx?FamilyID=bb196d5d-76c2-4a0e-9458-267d22b6aac6&DisplayLang=en)

SQL Server 2005 or above – To store imported performance data. You can also use SQL Server Express Edition

ReadTrace (RML Utilities for SQL Server)

x64 – http://www.microsoft.com/downloads/en/details.aspx?FamilyId=B60CDFA3-732E-4347-9C06-2D1F1F84C342&displaylang=en

x86 – http://www.microsoft.com/downloads/en/details.aspx?FamilyId=7EDFA95A-A32F-440F-A3A8-5160C8DBE926&displaylang=en

After installing all prerequisites, download the SQLNexus from the above site and store it any local folder and then extract the zip file SQLNexus3.0.0.0.zip

image

Double click or Run “SQLNexus.exe”

image

On loading of the application, it will prompt you to connect to SQL Server instance on which you want to create the SQLNexus database, specify the Server Name, Authentication information and click “Connect”

Its not recommended to run this on your production server, since it will create lot of performance loads while importing the data and running the reports

If RML Utilities is not installed, you will get the below error message

image

Without RML Utilities, Trace files (.trc) will not be imported

image

On successful connection, you will see the above Main Dashboard screen, To start the import process, click on “Import” link left hand bottom side of the screen

image

Click Folder Browse icon and select the folder on which your Output data from PSS Diag and SQL Diag tool is stored.

Please make sure there is no spaces in the folder names, If you have space in the folder name, you will  not be able to import the Trace files

To customize the import options, click on “Options”

image

Select the required options and if you want to retain the options you selected, click on “Save My Options”, for any reason, if you need to reset all the options you selected to default options, click on “Restore Default Options”

Use “Drop Current DB Before Importing” option to drop and recreate the database every time, this option will be helpful, if you want to load clean set of data every time.

Once finished customizing the options, click on “Import” button to proceed with the import

image

You will receive the above warning message, if you have selected “Drop Current DB Before Importing” option. click “Yes” to proceed with drop database and import

image

The above screenshots shows the import process and list of various files imported along with the number of records imported and also shows us whether the file imported successfully or not.

If you notice, the second line, Import for DBCC_MEMORYSTATUS_Startup.out file has failed, this is a known issue, there is no report currently available for DBCC MEMORYSTATUS, due to which this import will fail always, to follow up about this issue, please visit this discussion link CodePlex discussion forums http://sqlnexus.codeplex.com/workitem/11455 

Now the import has completed successfully.

Click on File Menu and then click on “Run All Reports” to generate the reports

image

After the reports are generated, the Report link icons will change as follows

image

Since we don’t have any data related to SQL Server 2000, it will remain unchanged.

In the next post, we will explore the reports generated by SQLNexus and see various information available through them.

I hope you all find this information useful. Please post your comments !!!

Advertisements

PSSDiag / SQL Diag Manager–How to install, configure and collect performance data from SQL Server 2005/2008/2008 R2 ?–Part 1

May 29, 2011 4 comments

PssDiag / SQL Diag Manager is a freely available graphical tool in CodePlex (http://diagmanager.codeplex.com/). It provides easy way to configure what data needs to be collected from SQL Server and Windows Server for troubleshooting SQL Server performance issues.

This tool is used by Microsoft SQL Server support team to collect data to troubleshoot performance issues.

Let us see how to install, configure and collect data

Step 1: Once downloaded the setup from the above specified link, run the Setup.exe and click “Next” on Welcome screen

clip_image001

Step 2: Select your installation folder and then click “Next”

clip_image002

Step 3: Click “Next” to confirm the installation

clip_image003

Step 4: Click “Close” to confirm the installation

clip_image004

Step 5: Once the installation is successfully completed, click on Start Menu and open “PSS Diag” and then click on “PSSDiag Configuration Manager”

image

Step 6: To configure the PSSDiag, Please specify the following information Machine Name (SQL Server Name), Instance Name (If Default Instance, specify *), Select authentication

clip_image006

Trace Template

By default, SQL Server 2008 is selected, PerfMon counters associated will be selected and for Database Engine, there are 3 trace templates available

image

Select the Trace template and specify the Maximum size, If you set it to “None”, file size will be unlimited, Please make sure you collect only Trace information for required events, selecting all events will degrade the performance of your SQL Server

Custom Diagnostics

Select the custom diagnostics you wish to collect, “SQL Memory Error” and “SQL Blocking” is not selected by default, these 2 are useful Custom Diagnostics to troubleshoot any blocking issues or long running query issues

image

Even though You see option to Select “SQL Server 7.0” and “SQL Server 2000”, this utility doesn’t support those versions of SQL Server.

clip_image007

Step 7: To configure Analysis service engine collection information, click “Analysis Services” tab, you can customize the PerfMon counters, Trace information and Custom Diagnostics

clip_image008

Step 8: Save the configuration – Once finished configuring, click on “Save” button on top

clip_image009

Specify the path on which you want to save the configuration output file and click “Ok”

clip_image010

Specify whether you are planning to run this on SQL Server 2008 or SQL Server 2008 R2

clip_image011

Once the configuration files are generated and packed as CAB file, the following message will be displayed. Click “Ok” to complete configuration and Exit the tool

Step 9: Run the PSSDiag and Collect the performance data

Open Window Explorer and go to the folder where the PSSD.CAB file is stored and then open it and extract the files. To extract the data, you can use any Zip tool such as 7 Zip or Win Zip or WinRAR

Once extracted, double click on “PSSDiag.cmd” or run it from Command Line

clip_image012

Click “Yes” to proceed with running the utility to start collecting performance data

SQLDiag utility will start with specified configuration and will display series of messages as displayed below

clip_image013

clip_image014

You can let run the tool as long as you want to collect the data. To stop collecting the data and exit the Tool, Press “Ctrl + C”.

The output from SQL Diag will be stored in folder named “Output”, from the folder where you are running the tool, Ex. If you are running the tool from “C:\Workspace\PSSConfig\”, then the output folder will be “C:\Workspace\PSSConfig\output”

clip_image015

The output folder will contain lot of different files containing the information collected.

In the next post, we will see how to import this collected information, run reports and find out how to identify the performance issues.

SQL Azure Query Tool– Part 4–Hannsens SQL Azure Manager

May 27, 2011 Leave a comment

Hannssens’ SQL Azure Manager is a lightweight tool for managing SQL Azure databases.

Its still in Alpha release since 25-Aug-2009 and built on .Net Framework 3.5 and available as ClickOnce installer from the below link

http://hanssens.org/tools/sqlazuremanager/

Step 1: Click “Run” to install the application and Run

clip_image001

Step 2: To login to SQL Azure server, specify the Server Name, Username and password and click on “Connect”

clip_image002

Step 3: In Main screen toolbar, you have the option to switch between databases, you can pick the database which you want to work

clip_image003

Based on the database you selected, the Object Explorer will be refreshed and display the Tables and Views, you can also use “Refresh” option in toolbar to manually refresh as well.

clip_image004

For Tables and View, you have the following 3 options available under Context menu

1. Select Top 100 Records

2. Select All Records

3. Delete the table

clip_image005

Step 4: You can write and run your queries on the Right Side Query pane, the results will be displayed in Grid format, There is no Text format support or messages pane, Error messages will be displayed as Message Popup

clip_image006

Step 5: To Create a new database, click “New Database” option in Toolbar and then specify the Database name and click “Ok”

clip_image007

Step 6: To delete a database, click “Delete Database” in toolbar and then click “Yes” to confirm

clip_image008

Missing Feature:

1. No Message Pane

2. No option to view / Modify or Create Stored Procedures / Functions / Triggers using User Interface

Conclusion:

There are no new releases in last 2 years and  the code was not released to Code Plex or Source Forge, so I am not sure about the future releases of this product.

Its also unstable and crashes.

User Interface is lightweight and nice and fast. It would be good, if they continue the development or release the code under CodePlex or other Open Source community

SQL Azure Database Manager–Project Houston

May 26, 2011 Leave a comment

In the Episode 3 of SQL Azure Query Tool, we will see how to use SQL Azure Database Manager 10.0 (Project Houston), which is part of Windows Azure Management Portal now

Step 1: Login to Windows Azure Portal (https://windows.azure.com/default.aspx ), select the SQL Azure Server and Database and click on “Manage”

clip_image002

Step 2: Accept the Terms and Conditions and data collection agreement and click “Ok” to connect

clip_image003

Step 3: Server name, database name and user name will be pre-populated, Please enter password and click “Connect” to login

clip_image004

Step 4: Start Page will display the database properties (Default Collation, Compatibility Level, Date Created and Read Only) and Server Edition and Version

clip_image006

Click on Left or Right Arrow near to Database Properties to Spin up to Resources view

clip_image008

Step 5: To Query data click on “New Query” in tool bar

New Query

clip_image009

Type the SQL query and click on “Execute” to run the query, you can Save the query as .SQL file for future use as well. Results of the query will be displayed in Bottom pane as Grid

clip_image011

Output Messages can be displayed in the “Message” tab

clip_image012

Step 6: To Create New Table – Click on “New Table” icon in toolbar

clip_image014

Enter the column name, select the data type, size, default value, Specify whether Identity, Required or Primary Key. Click on “+ Column” to add more columns. Once completed the design, click on “Save” to save the table.

Step 7: To add data click on Data View in toolbar

clip_image015

Click on “+ Row” to add rows and enters data. Click Save to store the data.

clip_image016

Step 8: Create New View: To create a new view, click on “New View” button in toolbar

Select Schema, Specify View Name and then type the SQL Query and click “Save”, After saving the query, you can click on “Data” to view the output from View

clip_image018

Step 9: Create New Stored Procedure: To create new Stored Procedure, click on “New Stored Procedure” button in toolbar

Select Schema, specify name of Stored Procedure and then click “New Paramter” or “+ Parameter” to add parameter

Specify the name of the parameter, select data type, specify size (if required), default value, whether output parameter or not

Then define the stored procedure body and click “Save”

clip_image020

Step 10: Execute Stored Procedure: Once saved, you can click “Run” and then specify parameter values and click “Execute”

clip_image021

clip_image023

Conclusion: SQL Azure Database Manager (Project Houston) is a light-weight Management Studio Express with limited functionality to create Table, Views and Stored Procedures and manage Data

Missing Features:

1. No option to define Synonyms

2. No option to define Functions / View functions

3. No option to create Logins

4. No option to create Users or Manage Permissions

5. No option to Import / Export data

I hope in the future versions, these missing features will be added to make it a full fledge and fully functional Database Manager.

I hope you all find this information useful.

SQL Azure Query Tool–myLittleAdmin

May 25, 2011 Leave a comment

myLittleAdmin for SQL Azure is web-based interface to manage SQL Azure server and databases

myLittleAdmin is available for free under Public Beta, but it will be charged once it goes live. Pricing information is not disclosed yet.

You can Install myLittleAdmin for SQL Azure in your own IIS server or you can access it directly from http://mylittleadmin4sqlazure.cloudapp.net/ 

For list of features and support information, please visit http://azure.mylittleadmin.com/azure/en/default.aspx

Login – To connect to SQL Azure server, provide Servername, Database, Login and Password to login to your server

image

You can also connect without specifying the database name

To Encrypt the connection or change the Connection Time out click on “Options” and select the required option

image

Click “Connect” to connect to your SQL Azure server

Dashboard – Databases Tab (Default) displays the available System and User databases

image

Dashboard – Connection Tab displays the License and Connection information

image

image

Server Info displays the detailed information about the connected SQL Azure server

image

To disconnect from the server, use the “Disconnect” option in “Connection” tab.

image

Database Tab To Manage Database Object

Click on User Databases and select your database to view Tables, Views, Synonyms, Stored Procedures, Functions, Users, Roles and Schema

image

By selecting a Table, you can access the following functions in the Right side pane

image

image – Open the table to view or modify data
image

You have option to Export to CSV, XML and Excel 2003 format. You can also filter data, modify or insert or delete records

image – Table Designer

image

image – View Dependencies

image

image-View Properties

image

image-View Permissions

image

Creating Views

To create View, Click on “Views” node and click on “New”  image Icon on Right side pane, and then modify the template to specify View name, Schema Name and SQL query to create view and click “Create”

image

Creating Synonyms

To create Synonyms, Click on “Synonyms” node and click on “New” image Icon on Right side pane, then enter the Synonym name, select the Schema and then Object Type and name and click “Create”

 image

Similarly you can create or modify or drop Stored Procedures, Functions, Database Triggers and View Assemblies and Types available

image

Manage Database Users, Roles and Schemas

image

Security Tab: Manage Logins, View Server Roles

image

To create new login, click on New Icon in Right side pane after selecting Logins node

image

image

Available Tools

image

You can use “New Query” tool to run adhoc queries, Type your query and click on “Submit”

image

Query options available

image

You can view the results of Query in Results tab

image

You can view Informational and Error messages in Messages tab

image

Generate Insert script for Tables

image

Select database, and table and specify the columns and then click on “Generate” to create insert SQL script

Generated Insert Script

image

To save the script, click on “Save As” and proceed

Import CSV Files

image

Options for import

image

I tried couple of different format and options, even after Import option is not working even after directly trying to import files exported from Data View itself

Always getting the following error

image

I wasn’t sure why its not able to see the table from Import option even for an administrator account

Preferences Tab

image

Under Preferences, you can select you preferred Language, available languages are English, French, Deutsch and Japanese.

Currently there is only one Skin and Export option available.

You can access the myLittleAdmin forums from Help tab.

Conclusion: myLittleAdmin for SQL Azure is a full fledge light weight Management Studio, which  supports all database objects, comes with useful handy tools and also available in 4 different languages.

When compared to Project Houston and Red Gate Query Anywhere, myLittleAdmin supports more features and also fast performing.

For detailed comparison of features across Project Houston, Red Gate Anywhere and myLittleAdmin for SQL Azure, please read this document

http://azure.mylittleadmin.com/azure/Comparing_SQL_Azure_Management_Tools.pdf 

I hope you all find this information useful !!!

SQL Azure Query Tool – Red Gate Query Anywhere

May 24, 2011 Leave a comment

Red Gate Query Anywhere is a web based tool to Query a SQL Azure database from anywhere. Its available for free in

http://www.queryanywhere.com/

Step 1: Please provide your Server name, Database, Username and Password and click Connect

clip_image001

Step 2: Once connected, you will be able to the Objects available in your database

clip_image003

Step 3: To Query the tables or view, you can directly start writing queries in Query pane and click “Execute”

clip_image005

Step 4: You can also create Stored Procedures

clip_image006

Step 5: To view the definition of Existing Stored Procedure or any other object, click on the Object name and expand and then click “ScriptAsCreate”

clip_image008

Conclusion: Red Gate Query Anywhere is nice and lightweight tool to query SQL Azure database, but still needs lot of improvement, such as

1. Showing the progress of executing queries

2. Object Name are displayed as Blank some times, which is eventually fixed after refreshing the UI

3. Option to login without providing database name and then pick a database and query

Friends, Play with this nice tool and post your feedback and comments !!!

In the next post we will review the other similar tools available

Project Houston – Microsoft SQL Azure Database Managerhttp://windows.azure.com

myLittleAdmin for SQL Azure (Currently in Beta, Pricing not announced) – http://mylittleadmin4sqlazure.cloudapp.net/

Hannsens SQL Azure Manager (Free) – http://hanssens.org/tools/sqlazuremanager/

Tech.Ed 2011 Session Videos available online

May 23, 2011 Leave a comment

Tech.Ed 2011 Session Videos available online for all sessions are available online from

http://channel9.msdn.com/Events/TechEd/NorthAmerica/2011

And also from

http://northamerica.msteched.com/contentcatalog?ck=no 

Channel 9 has the advantage of downloading the videos as MP4 or WMV.

If you are planning to download multiple videos, the following Silverlight tool will be helpful

http://dayngo.com/channel9/Download/15928 

Thanks Yuriy Lyeshchenko for putting together this wonderful download tool !!!

Categories: General Tags:
%d bloggers like this: