SearchSQLServer.com

• Home
• Topics
• Microsoft SQL Server Administration
• Microsoft SQL Server Performance Monitoring and Tuning
• SQL Server 2000 Query Analyzer: 10 tricks for simple querying

SQL Server 2005. Instead, Microsoft has built its querying features into SQL Server Management Studio. My impressions of SQL Server 2005 have been nothing but positive, but I must ask Microsoft: Would you please offer a lightweight querying tool?

Query Analyzer's performance gains can be divided into two broad categories:

1. Improvements in the raw performance of a SQL Server statement
2. Improvements in your job performance as a SQL Server DBA or developer

Here I will show you why and how SQL Server 2000 Query Analyzer is more than just a simple querying tool.

Tip 1: Graphical Query Plans Overview One of the best interfaces for understanding and improving the underlying SQL Server parser is the Graphical Query Plans only available in Query Analyzer. Based on a T-SQL statement, the parser will show the steps SQL Server takes to return your query. How do you enable it? Navigate to Query Menu | Show Execution Plan | Show Server Trace or Show Client Statistics. How do you read them? For the Show Execution Plan, read from right to left and hover your mouse over the icon on the lower pane for additional information. First focus on the processing that requires the most resources and determine how to improve the query performance. Additional resources Graphically Displaying the Execution Plan Using SQL Query Analyzer
2: Object Browser Overview The object browser has two tabs: Objects Tab and Templates Tab (outlined in Tip 3 below). The Objects Tab permits you to browse all of the database objects; drag and drop database object names by left clicking and pulling to the right pane; determine the definition of the objects; and access common system functions with the same drag-and-drop functionality. How do you enable it? Navigate to the Tools menu | Object Browser | Show/Hide. For your information Make sure you refresh the Object Browser by right clicking on the object that's higher in the hierarchy and selecting "Refresh," or press F5 to see the latest definitions and code. If something is missing or looks incorrect, refresh. Additional resources Using Object Browser
Tip 3: Object Scripting Overview An observation from working with many DBAs and developers is that they are familiar with Enterprise Manager's functionality to CREATE or DROP an object, but they were unfamiliar with the Query Analyzer's functionality, which can do the job just as well. Why leave Query Analyzer when this tool can do the job? How do you access it? Right click on the object and select the "Script Object to New Windows As" option. A context-sensitive menu will appear to generate a basic CREATE, ALTER, DROP, SELECT, INSERT, UPDATE, DELETE or EXECUTE. This is a quick-and-easy approach for interacting with the object you need without leaving Query Analyzer.
Tip 4: Templates Overview Who needs to go to SQL Server 2005 Books Online when Query Analyzer has coding templates? Do you know where they are located? They are on the Templates Tab in the Object Browser. Where are the templates stored? The interface is populated with the folders and *.tql files located by default at C:\Program Files\Microsoft SQL Server\80\Tools\Templates\SQL Query Analyzer\. The beauty of it is that you can add your own folders and files for common code you issue. For your information If you add new files and folders to the templates directory, make sure you close all of your sessions with Query Analyzer and re-open them to access the new folders and files.
Tip 5: Object Search Overview Have you ever lost an object in SQL Server? You know it is there, but not exactly where. Object Search is the solution to this problem. It is able to query across all databases from one interface. How do you access it? Navigate to the Tools menu | Object Search | New. Complete the options on the interface and press "Find Now" for the object.
Tip 6: Manage Indexes Overview Manage Indexes is another interface that few DBAs and developers seem to be aware of. Enterprise Manager has this capability, but why leave Query Analyzer to create, alter or drop existing indexes via a simple interface? How do you access this interface? Navigate to the Tools menu and the Manage Indexes option. For your information This is a simple, yet powerful interface. Heed this caution: When you are working in a production environment and drop, create or alter an index, the performance implications can be high. This type of work in a production environment should be planned and performed during a maintenance window
Tip 7: Manage Statistics Overview A similar interface and performance gain to Manage Indexes is Manage Statistics. This interface gives you the opportunity to create, update or drop statistics. How do you access this interface? Navigate to Tools menu | Manage Statistics option. For your information This is a simple, yet powerful interface. Heed this caution: When you are working in a production environment and drop, create or update statistics with a high sample rate, the performance implications can be high. This type of work in a production environment should be planned and performed during a maintenance window.
Tip 8: Bookmarks Overview One handy way to navigate or troubleshoot code is to set up bookmarks. The bookmarks become light blue boxes in the left margin of the query window, which can be easily seen. You also have the option to use keyboard shortcuts to navigate through many bookmarks. How do you access the bookmarks? Navigate to Edit | Bookmarks | Toggle Books, Next Bookmark, Previous Bookmark or Clear All Bookmarks.
Tip 9: Keyboard Short Cuts Overview Become a keyboard maestro with Query Analyzer using its many short cuts.	Execute a Query – CTRL + E Show or Hide the Results Pane – CTRL + R Comment Code – CTRL + SHIFT + C Uncomment Code – CTRL + SHIFT + R Results in Text – CTRL + T Results in Grid – CTRL + D
Tip 10: Query Analyzer Options Overview The Options interface has a number of tabs for many of the configurable items in the tool. I see this interface as similar to the Microsoft Office interface and appreciate the options to universally change items across the application. How do you access it? Navigate to Tools menu | select Option selection.

About the Author: Jeremy Kadlec is the Principal Database Engineer at Edgewood Solutions, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and delivers frequent presentations at regional SQL Server Users Groups and nationally at SQL PASS. Jeremy is also the SearchSQLServer.com Performance Tuning expert. Ask him a question here.

---

More information from SearchSQLServer.com

• Downloads: Download SQL Server tools
• Tip: SQL Server 2000 index fragmentation
• Topic: Research performance tuning best practices

---

More from Related TechTarget Sites

• Business Analytics
• Data Center
• Data Management
• Data Management UK
• Oracle
• Content Management
• Windows Server

• Business Analytics

  • For unwary users, social media data analysis can be an unfriendly task

    Organizations must take into account various obstacles when implementing social media analytics plans to ensure that they provide business value.

  • Using social media monitoring tools starts with business connections

    Companies implementing social media analytics programs need to keep the big picture in mind and tie the efforts directly to their business strategies.

  • Social media analytics software pulls useful info out of online muddle

    Companies can use social media analytics tools to distill business insights from the abundance of data on social networks. That's the goal, at least.

• Data Center

  • The drivers of DevOps movement: Process, automation, collaboration

    Get your team to work together by following the principles of DevOps movement: effective processes, data center automation and staff collaboration.

  • Network virtualization technology headed to the enterprise

    Movement to new network virtualization technology is taking shape, but vendors have to clear many hurdles before deployments take place.

  • An in-depth look at IBM's transactional memory support facility

    IBM's zEC12 mainframe introduced support for transactional memory. Learn the basics of Transaction Execution Facility.

• Data Management

  • New Informatica Cloud update promotes business user self-service

    The winter release of Informatica Cloud aims to increase business user self-service capabilities while improving data quality.

  • Effective 'big data' strategy helps advertising firm attract clients

    Online advertising company adMarketplace uses a mix of NoSQL and SQL databases in a "big data" environment that feeds its search syndication system.

  • Successful data stewardship framework needs solid plan, firm focus

    Stewarding data can be a tough nut to crack: lots of effort for a reward that isn't always apparent. To succeed, strong project management is needed.

• DataManagement UK

  • Big data roundtable webcast: gaining value from big data

    Roundtable discussion on business value of big data, chaired by Brian McKenna, Editor, SearchDataManagementUK, and featuring Mike Ferguson, Roxane Edjlali (Gartner), Joshua LeCure (GlaxoSmithKline).

  • 2012 training courses: BI, data management, data warehousing

    Find out where to hone your business intelligence, data management and data warehouse skills with this list of training courses for 2012.

  • Poor data quality: what is dirty data costing UK organisations?

    Poor data quality can have serious financial consequences for businesses. Read about the current state of data quality management at UK companies, including dirty data problems.

• Oracle

  • Accessing Hyperion financial information from mobile devices

    Pushing Oracle Hyperion financial information to mobile devices means considering what data to push there and what kind of devices your workers have.

  • Oracle Day: A mix of Oracle career day, field trip and pep rally

    Is going to Oracle Day worth the trip? If you're going for Oracle career networking, yes. But is the information useful?

  • Using SQL SELECT and SQL UNION ALL statements to sort numbers

    Expert Dan Clamage explains how to use SQL SELECT and SQL UNION ALL statements to sort and visualize a set of sales figures.

• Content Management

  • Collaboration expert: Let business drive technology adoption efforts

    Susan Hanley, an enterprise technology adoption expert who specializes in SharePoint, shares her vision for adoption success.

  • Are 'intranet' and 'extranet' outdated words? Are there better terms?

    The terms customer-, partner- and employee-facing websites are replacing intranet and extranet in the IT lexicon and better explain the purpose of a website.

  • Successful social collaboration efforts have common key facets

    Three examples of companies that have taken different routes to implementing enterprise social collaboration demonstrate that they share similarities that might explain their success.

• Windows Server

  • Windows Server 2012, Windows 8 receive first patches

    Two months after general availability, Windows Server 2012 received its first Patch Tuesday fixes.

  • Windows Server Failover Clustering gets boost from PowerShell cmdlets

    In Windows Server 2012, there are 81 new and improved failover clustering PowerShell cmdlets. Here's how to make the most of their capabilities.

  • FAQ: troubleshooting common errors in Microsoft PowerShell

    In this FAQ, we have our resident PowerShell expert answer some common questions to help admins troubleshoot errors in this scripting language.

• About Us
• Contact Us
• Site Index
• Privacy policy
• Advertisers
• Business partners
• Events
• Media kit
• TechTarget Corporate site
• Reprints
• Site map