Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

SQL Server Software Generate Code, Secure Your Database, and More

DZone's Guide to

SQL Server Software Generate Code, Secure Your Database, and More

With this tool, you can create tests, check performance, write code faster, include more tools to develop, test, and monitor SQL Server in an efficient manner.

· Database Zone ·
Free Resource

Slow SQL Server? These SentryOne resources share tips and tricks for not only troubleshooting SQL Server performance issues, but also preventing them before they hit your production environment.

I have been working with SQL Server for 18 years and I was looking for a good way to develop my software, scripts, and stored procedures faster and more efficiently.

After testing several tools, I found this nice SQL Server add-on for SSMS.

It contains the following components:

  1. First, SQL Complete will help you complete your T-SQL sentences faster. It will complete your sentences clauses and generate code automatically.
  2. Secondly, Search will help you find not only objects but also data in tables.
  3. The next software is Documenter for SQL Server. This add-in will help you generate documentation automatically and save the documentation in different formats like HTML and PDF.
  4. Data Generator is another extremely powerful tool that will allow you to generate realistic data for testing purposes.
  5. Another interesting tool is Source Control. This tool allows you to administer database changes in a source control software like SVN, TFS, Perforce, Git, Mercurical, and SourceGear Vault.
  6. Also, we have Monitor, which will not only monitor but also analyze the database performance.
  7. Query Builder is another powerful tool that will allow creating complex queries in seconds.
  8. Unit Test will help us detect bugs by executing a unit test to verify if there are errors in the code.
  9. The indexes are always one of the simplest and cheapest options to improve performance. With Index Manager, you can receive useful index recommendations and help with fragmentation problems.
  10. Event Profiler is used to analyze data and sessions. You can monitor the database engine, SSIS activity, and SSAS activity. It also helps to identify performance problems, errors, and warnings. Also, you can analyze execution plans.
  11. Schema Compare is used to compare database objects like tables and stored procedures. You can not only compare but also synchronize the databases.
  12. Data Compare allows you to compare the data of tables or view and verify the differences between them. You will also be able to synchronize the data.
  13. SQL Decryptor is useful when you encrypted your code and you need to recover it. With SQL Decryptor, you can decrypt different objects like stored procedures, functions, and triggers.
  14. Data Pump is used to import and export data. It supports different formats like JSON, CSV, XLS, XML, ODBC, and more.

Event Profiler

Let's start with the Event Profiler. Open the dbForge Event Profilerand press New to create a new trace:

Image title

We will analyze the activity. In the General page, add a connection to your SQL Server. You can specify the template when to stop the trace and the maximum size of the trace. Enable rollover and specify the maximum number of files:

General dbforge Event Profiler Properties

There are several templates available for you to monitor locks, query batch sampling, query detail sampling, TSQL locks, and more:

Image title

You can capture different events like SQL batches and login and logout events. In Events to Capture, you can select the events to trace:

Image title

In Actions, you can select many options like the call stack, the name of the client application that is using SQL Server, the hostname, the Process ID, CPU ID, and more. You can check some of the options in the picture below:

SQL Server actions

There are other interesting options like the system used to check if the session is a system session or not. You can also get the NUMA node ID, query hash information, and more:

Image title

In the Event Filters page, you can create your own filters to customize the information to trace:

Image title

Finally, in the Columns page, select the columns to trace:

Image title

You can check the sentences highlighted.

Image title

You may find Event Profiler equal to the traditional SQL Server Profiler included with SQL Server at first view. However, it includes several advantages.

As you may know, SQL Server Profiler will be removed in future releases and it will be replaced with the extended events. This is because SQL Profiler is very slow.

On the other hand, Event Profiler offers several advantages like the following:

  • dbForge Event Profiler does not consume a lot of resources like the Native SQL Profiler. In SQL Profiler, it is recommended to run it on a remote computer to avoid resource consumption problems. This is not the case with Event Profiler.
  • It supports extended events, which means that there are events in SQL Server Profiler no longer supported (because it will be deprecated soon). This tool can trace 1,000+ events!
  • The tool was designed to work with an optimized performance consuming low resources.
  • You can filter and sort data in the grid to narrow your results.

dbForge Monitor

One of my favorite tools is Monitor. It is used to check the SQL Server performance. To start, right-click your database and select Show Monitor:

Image title

It will show you the CPU utilization, Memory utilization,and Disk activity in MB using graphs online to monitor it. It will also show batch requests, deadlocks, transaction per second, full scans per second, and more. You can also see the SQL Server properties like the authentications, collation, edition, virtual memory, max memory, and more:

SQL Server performance counter

The DataIO page will show you the statistics per database with read and write statistics and read and write latency:
Image title

In the Databases tab, you can check which databases are online, offline, the recovery model (simple, FULL, or Bulk), the compatibility level, and size. What I love about this tab is that you can check all your databases at once:

Image title

The fourth tab contains the Wait Stats. It contains statistics like the wait count, wait time, and average time. This information is useful to check if there are performance problems.

Image title

One of the most useful tabs is Top Queries. With this tool, we can detect which queries are consuming most of the resources in our database. We can check the plan diagram, plan tree, and plan in XML to analyze the query directly in detail and find out how to optimize the query (if possible) or at least detect it:
Image title

The sessions will show the active sessions in SQL Server. It is useful to monitor the database being used.

Image title

Finally, we have the option to check the backups per database and the path.

Image title

In future articles, we will explore some of the main features of the other tools. As you can see, they include useful and powerful options.

Conclusion

If you are a DBA or a developer in SQL Server, this tool will save you a lot of time. You can create tests, check performance, write code faster, include more tools to develop, test, and monitor SQL Server in an efficient manner.

References

Database monitoring tools letting you down? See how SentryOne empowers Enterprises to go faster.

Topics:
sql server ,database ,database security ,ssms

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}