Today is the first day when we turned SQLTreeo into a commercial product and your emails continue to flood into our mailbox, with the overall message that you don’t like price. We are listening and we want to respond. SQLTreeo is not an entirely business-driven project and we are able to change the price, and we have. Check our website for the new one. Thanks for all your feedback and we hope you will like the new pricing.Tags: download, SQLTreeo, SSMS, SSMS Add-In
New version of SQLTreeo SSMS Add-In was released. We fixed few bugs and include also one major change – SQL Server 2012 support. SQLTreeo Add-In also became commercial product which will help us to give more attention to further development and support. We are now working on local storage support which was desperately demanded by almost everybody who used SQLTreeo in the past. However we will still support current way of storage using extended properties. You can download new version here.Tags: Add-In, databases, development, folders, grouping, SQL, SQL Server, SSMS, SSMS Add-In, treeo
I would like to inform you that I transferred all activities around SQLTreeo SSMS Add-In to couple of persons who will be running web site and add-in further. This step was inevitable as I have no chance to cultivate it in the future.
There are few improvements I highlighted and they should be addressed after handover:
- Change storage of folder structure from extended properties to local file.
- Add SQL Server Management Studio 2012 Support
- Add ability to organize SQL Server Agent Jobs and Diagrams in folders
- Fix some bugs
I hope that there will be more, it will be driven mainly by power of your demand. Contact and other important information are on the www.sqltreeo.com.Tags: SQL Server, SQL Server Management Studio, SSMS, SSMS Add-In, treeo
I was trying to solve SQL Treeo’s performance issue on slow connection. This posts is also covers feature request of having separate folder structures for each developer (store folder structure in local file). SQL Treeo currently stores object’s folder name to its extended property named “VirtualFolder”. This helps to identify which object belongs to which folder.
Advantages of this apporach:
- folder integration is completely seamless as it’s part of database data (extended properties of e.g. stored procedure)
- if it’s shared database, other developers with SQL Treeo installed have access to same folder structure
- it’s slower on non-local connections. It’s because of fetching extended properties using fn_listextendedproperty one-by-one. For database with thousands of objects accessed via e.g. VPN, it’s slow. (fetch all extended properties in batch is possible but not usable within add-in).
- it’s limit for storing more attributes to folders (such as color, completely own structure etc.)
Based on that, I would suggest to completely get rid of storing folder name to extended properties and store them to local file instead.
- performance, no access to database is needed (probably only EXISTS for objects within folders)
- each user could have its own folder structure
- more flexibility for future enhancements (such as mixing more SQL object types in one folder.)
- local file will not be shared with other database developers by default. However this could be solved by storing this file to any source control or placing it to network shared folder.
Please let me know if you agree with new “local file” approach.
Use this poll:
Thanks.Tags: Add-In, custom folders, denali, download, Microsoft SQL Server, productivity, refactoring, SQL, SQL Server, SSMS, table, treeo, view
one of SQL Treeo users developed clever workaround for SQL Treeo SSMS Add-in to be more compatible with RedGate SQL Search. Enhancement causes that RedGate SQL Search can now jump in folders you created using SQL Treeo Add-in. This allows you to organize your SQL objects in folders and search them using RedGate SQL Search.
Patch can be downloaded here.
Tags: Add-In, custom folders, denali, download, Microsoft SQL Server, productivity, refactoring, SQL, SQL Server, SSMS, table, treeo, view
I was using various “quick find” SSMS add-ins which give you ability to get your tables, stored procedures, functions and other objects quicker than by using standard SSMS features. Few of them were very well usable but all of them are built to support also complex searching including schema, source code occurence, object type etc. etc. This is basically good feature but if you’re inside furious T-SQL coding you just want to jump accross objects with quickest way possible – every filter criteria or detail you must take care of is slowing you down.
This is the ideal situation for me:
1) press e.g. CTRL+T to bring tiny search window
3) enter part of object’s name
4) choose object and press ENTER to bring ALTER script or open window where this object is already opened
I prepared prototype of add-in which perfectly fulfills my requirements, sample screenshot is here:
Here is also short video showing some more use cases.
Feel free to comment.Tags: Add-In, filter criteria, quick find, search, search window, SQL, SSMS, SSMS Add-In, Stored procedure, t-sql
new, second version of SQL Treeo productivity add-in was released.
For little reminder, here are main features of this add-in:
- Create custom folders in SSMS’s object explorer using drag&drop. It works for databases, stored procedures, functions, views and tables
- Ctrl+click on object will bring ALTER script
- Perform bulk operations in explorer-style management dialog – I did that because while I was refactoring and re-foldering more objects, drag&drop was not enough
Since last version I worked especially on supporting all version of SSMS, fixing major bugs and on adding few more features. From outside view it seemed I worked with snail speed because first version of my add-in was release 3 month ago. Truth is that I develop this add-in only in my spare time which is very rare and that’s main reason why I am so slow
Here is humble change log:
- SSMS 2005, 2005 Express, 2008, 2008 Express, 2008 R2, 2008 R2 Express and Denali versions are now supported – I didn’t do comprehensive testing of all versions but it “should” work.
- Add-in caused exceptions when used for some unsupported folders (e.g. maintenance plans). It should be ok now.
- Folder names are now case sensitive.
- When Offline databases were present, add-in failed badly. Fixed.
- Ctrl+Click didn’t work for triggers . Fixed.
- Add-in didn’t handle apostrophes in names very well. Fixed.
- “Collapse all folders” feature was added.
- Dragging object from object explorer to opened window didn’t result in pasting text as usual. Fixed.
- Support for non-English SSMS versions added.
- Slight performance improvement. But SQL Treeo add-in is still meant to be either for local databases or for quick networks.
SQL Treeo add-in is here for download.Tags: Add-In, custom folders, denali, Microsoft SQL Server, productivity, refactoring, SQL, SQL Server, SSMS, table, treeo, view
SQL Server 2011 “Denali” CTP3 introduced couple of new Dynamic Management Views (DMV). However, documentation says that some of them were already introduced in SP1 of SQL Server 2008 R2 but it was only two month ago (July 2011). I believe that these views were developed in new release (“Denali”) but were so impactless that they were inserted to the latest SP of 2008 R2. Here are four of them which I find quite useful:
This DMV displays information about Windows version, Service Pack level and Language of your operating system.
This is replacement for xp_reg_read, which is used for reading SQL Server instance-related Windows registry entries.
This is very useful – it returns state of Windows services related to SQL Server instance. You can find out whether SQL Server Agent or Full Text indexing service are running.
You can query which ports SQL Service listens on your server.
Tags: denali, DMV, listener, management views, operating system, ports, server agent, service pack level, SQL, SQL Server, SQL Server 2008 R2, sql server 2011, windows registry, windows version, xp reg
SQL Server DMV Views might be very helpful. Let’s imagine that you have a couple of nested stored procedures and you find out that it’s slow. It would be nice if you could discover which single query/ies in your procedures causes that it is slow. Few DMV views can tell you that.
Ian W. Stirk wrote whole book about practical use DMV views which is definitely worth to read. I’ve received comment from Ian to one of my post recently which contained helpful piece of advice I would like to share here.
Here is link to his book:
SQL Server DMVs in Action: Better Queries with Dynamic Management Views
He post me query which is using DMV view to basically order part of your stored procedure’s source code from slowest to fastest. After some time of using it I found out that it had really great value for me because it discovered things you couldn’t realize (e.g. that scalar functions are terribly slow).
Core of the query are two views – sys.dm_exec_query_stat and sys.dm_exec_sql_text.
Sys.dm_exec_query_stat query contains performance statistics for each query executed on your SQL Server instance. No rocket science. But it contains even offset which can lead you to exact portion of query which is slow – this means that you can find out statistics for e.g. one SELECT query in your procedure. Sys.dm_exec_sql_text contains source code of executed SQL objects. If you combine those two views together you will find out statistics for each individual part of your stored procedure.
Here is the query:
SELECT CAST(total_elapsed_time / 1000000.0 AS DECIMAL(28, 2)) AS [Total Duration (s)] , CAST(total_worker_time * 100.0 / total_elapsed_time AS DECIMAL(28, 2)) AS [% CPU] , CAST((total_elapsed_time - total_worker_time)* 100.0 / total_elapsed_time AS DECIMAL(28, 2)) AS [% Waiting] , execution_count , CAST(total_elapsed_time / 1000000.0 / execution_count AS DECIMAL(28, 2)) AS [Average Duration (s)] , SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1, ((CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS [Individual Query] , SUBSTRING(qt.text,1,100) AS [Parent Query] , DB_NAME(qt.dbid) AS DatabaseName FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt WHERE total_elapsed_time > 0 ORDER BY total_elapsed_time DESC
There’s one but – I found out these DMV views contain all statistics data from start of your SQL Server Instance. I didn’t find out how to display these statistics only for last query. I always restart SQL Server instance if I don’t want to get mix of slow queries which are not related to last batch I executed. It’s dumb but I didn’t make up better solution. If you know better solution, please share.Tags: Average, CAST, COUNT, DMV, duration, Microsoft SQL Server, performance statistics, scalar function, SELECT, SQL, SQL Server, SUBSTRING, view, WHERE
Microsoft SQL Server guys extended periods for Denali’s feedback on Microsoft Connect. And they motivates with really nice gifts for 30$. All you have to do is to report a bug or make a suggestion till September 10, 2011 and be in 300 of first.
Please follow instructions in this post.Tags: denali, feedback, Microsoft Connect, Microsoft SQL Server, sql server 2011