Find Most Expensive Queries Running on a Table I was investigating SQL Server performance issue on a table where I was getting more locks, query timeout expires, on a table that has more than a hundred columns. So I was just wanted to know what are the most expensive queries running on that SQL Server table. I would like to share my script that will return most expansive query running on a table, Hare I am using DVM to find the most expansive query. Suppose we have a table called calls SELECT TOP 15 SQLQueries=SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1), qs.execution_count, qs.total_logical_reads, qs.last_logical_reads, qs.total_logical_writes, qs.last_logical_writes, qs.total_worker_time, qs.last_worker_time, qs.total_elapsed_time/1000000 total_elapsed_time_in_S, qs.last_elapsed_time/1000000 last_elapsed_time_in_S, qs.last_execution_time, qp.query_plan FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp where SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) like '% calls %' -- ORDER BY qs.total_logical_reads DESC -- logical reads -- ORDER BY qs.total_logical_writes DESC -- logical writes ORDER BY qs.total_worker_time DESC -- CPU time The above script will return the most expansive query running on “calls” table. you can change the order by clause on Logical reads, Logical writes or on CPU time. Please note : Running in your environment replace “calls” from script to your table name. ie % calls %

How to create and manage a very large database in sql?

Seema

I would like to know what it takes to get started with SQL in such a way that one would be able to create and manage an extensive database. Extensive not in the case of data but extensive in the case of having complexity as there are many sub-products related to the main products and also employees and many other departments are related to it each product.

I am a python developer and have knowledge of sqlite3 and know how to do CRUD operations in it. I have to build this app for a company and I would like to know what is the professional way of creating and managing a database.

What I mean is that what is the best way of storing information about each product and its sub-products. e.g just create one database and store information in individual tables or create separate databases for each product, sub-products, employees, daily progress, and many more data.

I have found many courses & learning materials that teach SQL but mostly are beginners and might not have what I need. I was wondering if there are people that are working in this domain and know how a professional would handle this type of project. During my research, I have also heard of some SQL techniques like T-SQL and SSIS. Don't know whether I need to implement them or not, that's why I am asking for a roadmap to let me get started in the right direction from the beginning.

And any course, youtube channel, blog, or any other website that might be useful in learning database managing and data storing techniques & tips in SQL.

I would appreciate your feedback. Thanks

NOTE: I don't know whether I was able to express myself clearly and in simple words or if it's just lack of my SQL knowledge. Do let me know and correct me where I am wrong.