For example consider a database that has many SP and many query execute on it using ORMs. It's very time consuming that check every SP or profile all queries that ORMs generate for performance and missing index problem.  Is there any way to get missing index statistics for all SP and queries?  thanks
123991
tool available to find missing indexes in mssql 2008 ? can we find the missing index using Database Engine Tuning Advisor
112267
I want to know if I using different databases can help performance.  I have one big table support_chat with more than 5 million rows. I need to scan this all the time (3s with ajax) to check new messages on support.  If I move this table to another database in the same computer, can it help anything? (let users, sells, comments table faster)  For example:  db1  users table sells table comments table support_chat table // move this table to db2   db2  support_chat table   They want to keep old things due legal reasons, to have a user support history.
110996
This next year, I am helping an effort to clean several SQL Server environments.    We have about 10,000 stored procedures and estimate that only about 1000 of them are used on a regular basis, and another 200 or so are used on a rare occasion, meaning we have a lot of work to do.    Since we have multiple departments and teams that can access these databases and procedures, we are not always the ones calling the procedures, meaning that we must determine what procedures are being called.  On top of that, we want to determine this over a few months, not in a few days (which eliminates some possibilities).  One approach to this is to use the SQL Server Profiler and track what procedures are being called and compare them to the list of what procedures we have, while marking whether the procedures are used or not.  From then, we could move the procedures to a different schema in case a department comes screaming.  Is using the Profiler the most effective approach here?  And/Or have any of you done something similar and found another way/better way to do this?
32770
How would I be able to get N results for several groups in an oracle query.  For example, given the following table:  |--------+------------+------------| | emp_id | name       | occupation | |--------+------------+------------| |      1 | John Smith | Accountant | |      2 | Jane Doe   | Engineer   | |      3 | Jack Black | Funnyman   | |--------+------------+------------|   There are many more rows with more occupations.  I would like to get three employees (lets say) from each occupation.  Is there a way to do this without using a subquery?
103513
A while ago I had a query that I ran quite a lot for one of my users. It was still being evolved and tweaked but eventually it stablised and ran quite quickly, so we created a stored procedure from it.   So far, so normal.   The stored procedure, though, was dog slow. No material difference between the query and the proc, but the speed change was massive.   [Background, we're running SQL Server 2005.]  A friendly local DBA (who no longer works here) took one look at the stored procedure and said "parameter spoofing!" (Edit: although it seems that it is possibly also known as 'parameter sniffing', which might explain the paucity of Google hits when I tried to search it out.)   We abstracted some of the stored procedure to a second one, wrapped the call to this new inner proc into the pre-existing outer one, called the outer one and, hey presto, it was as quick as the original query.   So, what gives? Can someone explain parameter spoofing?   Bonus credit for    highlighting how to avoid it  suggesting how to recognise possible cause discuss alternative strategies, e.g. stats, indices, keys, for mitigating the situation
25857
My aim is to audit all queries on an instance, who's running them and from where, how many reads, in order to consolidate/migrate on another instance.   Figured out the best way is via Extended Events to .xel files, over 2 weeks, copy those and analyse on my well-specd desktop. Set up the capture with the minimal number of details (fields), so really can't make it smaller than this - aprox 12GB in 12 files. Used "Merge Extended Event Files" in SSMS to load the .xel files and process as detailed in   Is this really the best way to do it ? Is there a better tool to load the files ? It's really slow with SSMS 17.7 - example: with only 4 filters so far (before getting into grouping and aggregates) it took over 12h to get to crunch through 50% of the logs.
81313
I'm starting a new application and looking at using an ORM -- in particular, SQLAlchemy.  Say I've got a column 'foo' in my database and I want to increment it.  In straight sqlite, this is easy:  db = sqlite3.connect('mydata.sqlitedb') cur = db.cursor() cur.execute('update table stuff set foo = foo + 1')   I figured out the SQLAlchemy SQL-builder equivalent:  engine = sqlalchemy.create_engine('sqlite:///mydata.sqlitedb') md = sqlalchemy.MetaData(engine) table = sqlalchemy.Table('stuff', md, autoload=True) upd = table.update(values={table.c.foo:table.c.foo+1}) engine.execute(upd)   This is slightly slower, but there's not much in it.  Here's my best guess for a SQLAlchemy ORM approach:  # snip definition of Stuff class made using declarative_base # snip creation of session object for c in session.query(Stuff):     c.foo = c.foo + 1 session.flush() session.commit()   This does the right thing, but it takes just under fifty times as long as the other two approaches.  I presume that's because it has to bring all the data into memory before it can work with it.  Is there any way to generate the efficient SQL using SQLAlchemy's ORM?  Or using any other python ORM?  Or should I just go back to writing the SQL by hand?
96223
Does oracle database suffer from parameter sniffing issue in stored procedures?  Considering there are millions of pages written on parameter sniffing issue, I think that this is more of a bug than a feature.  Also considering with Sql Server 2017 features like automatic query tuning, I was wondering is SQL Server late in fixing these estimates/plan caching issues compared to other Database management system?
120183
How many records should there be before I consider indexing my sql tables?
37353
We've been having a number of issues with our indexes lately which our DBA team has attributed to statistics not having been run recently. This has made me wonder - how can I check if statistics have been recently updated via SQL Management Studio?  I apologize if this question isn't explaining this very well - I've only been introduced to statistics until now and prior to this would look to indexes whenever I've had performance related issues.  Edit:  I'm using the following but receiving a syntax error:  use *databasename* exec sp_autostats *schema.tablename*   The error I'm receiving is:  Msg 102, Level 15, State 1, Line 2 Incorrect syntax near '.'.   Why is this?
108265
I need to set up some views that will pull some data from a custom table I created.  Some views will need to pull content the regular way and from my custom table (where I may query for a particular nid, etc.).  How do I do this, or where is a good place to research?
81073
We're working with somewhat large sets of data, around 15M - 35M records per DE, each row with 150+ attributes. What are some ways to optimize SQL queries when handling many records?  For an exaple, using intermediate DE's and SQL queries selecting TOP 50% from a DE with 14M records will still take approximately 21-27 minutes to run, meaning with any major data increase these queries will time out. Data flow is Synchonized DE -&gt; Shared DE -&gt; sub-BU DE per object.  Salesforce's  on optimizing Queries is really basic and not of use this time around. Here is as example Query SELECT TOP 50 PERCENT // attribute list // FROM Synchronized DE WHERE // conditions //  ORDER BY  // condition //
60878
I've recently finished a project, during which many DB tables were created.   Most of these tables contain temporary garbage, and I am looking for a simple way to list all these tables.  Is there a way to list all DB tables sorted according to their creation date?
13604
I have a piece of SQL that seems to run really fast in Environment A, but the exact same query runs really slow in Environment B!  Environments are supposed to be the same, so what should I do and/or where should I look in order to see why the query doesn't perform the same?
35521
I need to provide real-time, or almost real-time, data for SSRS and Tableau reporting. I don't want the production OLTP system to be negatively impacted by long running queries. Will running a large query on a secondary database in an availability group affect transaction performance in the primary database?
97492
I have a MySQL database table with nearly 23 million records. This table has no primary key, because nothing is unique. It has 2 columns, both are indexed. Below is its structure:    Below is some of its data:    Now, I ran a simple query:  SELECT `indexVal` FROM `key_word` WHERE `hashed_word`='001'   Unfortunately, this took more than 5 seconds to retrieve the data and show them to me. My future table will have 150 billion records, so this time is very very high.  I ran the Explain command to see what's going on. Result is below.    Then I ran the Profile using below command.  SET profiling=1; SELECT `indexVal` FROM `key_word` WHERE `hashed_word` = '001'; SHOW profile;   Below is the result of the profiling:    Below is some more information about my table:    So, why this is taking so long? They are indexed too! In future, I have to run lot of LIKE commands, so this is taking too much time. What has gone wrong?
81450
It is a known fact that the DMVs dont hold accurate information regarding number of pages and count of rows. However, when you have the stats updated, I can't see why they wouldn't.  I am working on a monitoring tool, want to know disk size of each index and data, etc. Eventually I would like to find the right fill factor, and other things etc.  The space used by my function and the old sp_spaceused differs a little bit on the space usage, but not on record count.  Can you see if there is anything missing in my select?  this is the sp_spaceused (then I convert the numbers in MB):  sp_spaceused 'tblBOrderRelationship' go  select 318008/1024.00 AS reserved, 140208/1024.00  AS data, 177048/1024.00 AS index_size, 752/1024.00    AS unused     But when I run my select, code below\picture below, I get slightly different figures.  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED  SELECT      schema_name(t.schema_id) as SchemaName,     t.NAME AS TableName,     t.type_desc,     t.is_ms_shipped,     t.is_published,     t.lob_data_space_id,     t.filestream_data_space_id,     t.is_replicated,     t.has_replication_filter,     t.is_merge_published,     t.is_sync_tran_subscribed,     --t.is_filetable,     i.name as indexName,     i.type_desc,     i.is_unique,     i.is_primary_key,     i.is_unique_constraint,     i.fill_factor,     i.is_padded,       sum(p.rows)               OVER (PARTITION BY t.OBJECT_ID,i.index_id)  as RowCounts,     sum(a.total_pages)        OVER (PARTITION BY t.OBJECT_ID,i.index_id)  as TotalPages,      sum(a.used_pages)         OVER (PARTITION BY t.OBJECT_ID,i.index_id)  as UsedPages,      sum(a.data_pages)         OVER (PARTITION BY t.OBJECT_ID,i.index_id)  as DataPages,      (sum(a.total_pages)       OVER (PARTITION BY t.OBJECT_ID,i.index_id)  * 8) / 1024 as TotalSpaceMB,      (sum(a.used_pages)        OVER (PARTITION BY t.OBJECT_ID,i.index_id)  * 8) / 1024 as UsedSpaceMB,      (sum(a.data_pages)        OVER (PARTITION BY t.OBJECT_ID,i.index_id)  * 8) / 1024 as DataSpaceMB FROM      sys.tables t INNER JOIN           sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN      sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN      sys.allocation_units a ON p.partition_id = a.container_id WHERE      t.NAME NOT LIKE 'dt%' AND     i.OBJECT_ID &gt; 255  AND T.NAME = 'tblBOrderRelationship'   the figures    the bigger picture, including the index names   Now doing some calculations to check the results:  --================================== -- the figures from sp_spaceused --================================== select 318008/1024.00 AS reserved, 140208/1024.00  AS data, 177048/1024.00 AS index_size, 752/1024.00    AS unused  --================================== -- the figures from my select --================================== select 137+61+56+54 AS reserved,        137 AS data,        61+56+54 AS index_size     It is not so far off, really, apart the fact I did not calculate the unused space!  What can I do to make this accurate?  AFTER CHANGES:  After I replaced 1024 by 1024.00 the results are much more accurate.  I noticed records have been inserted into the table in question, and obviously the stats are not so up to date, but still the results match (under 1 MB difference - which is all right for me)  The new result sets are:  --================================== -- the figures from sp_spaceused --================================== select 318072 /1024.00 AS reserved, 140208 /1024.00 AS data, 177096 /1024.00 AS index_size, 768 /1024.00 AS unused go  --================================== -- the figures from my select --================================== select 137.7578125+61.7968750+56.4218750+54.6406250 as reserved,        137.7578125 as data,        61.7968750+56.4218750+54.6406250 as index_size