New answers tagged

0

I'm pretty terrible at these DMVs however: When you're joining on the history you can have one plan_handle with multiple sql_handle (where the plan executes with different options), so possibly this is skewing your stats. The plan handle is for an entire batch, with individual statements inside it, which can be recompiled separately, and this might (I ...


0

Is there a way to find out what queries are run the most against a particular table? T-SQL – Get a Historical Record of SQL Statements Hitting a Table This query checks the sys.dm_exec_query_stats system view and the sys.dm_exec_sql_text table valued function. Also because these are system tables, you will need to be logged into SQL Server ...


0

I would like to give you some hints for testing on performance. In order to make such tests, and get the right profiling, you have to flush the contents from the database buffer cache (DBC) before each query, otherwise the database will store some of the needed blocks in the buffer cache and that will affect the performance each time. Also, keep an eye on ...


0

Try creating an index on column[DownloadDate] (if you do not have one) and then see whether the issue still persists. create index idx_DownloadDate on dbo.tbl_Download (DownloadDate);


0

Changing CPU affinity was never a common practice but did have it's uses on WindowsNT and later on Windows Server 2000/3, The main issue was that processor load could be misaligned on multiprocessor systems and this allowed for freeing up resources. This could also be helpful on systems that where not dedicated to running SQL Server. So yes this could be ...


5

If you are interested in locking, there are several extended events available: lock_acquired lock_released lock_escalation The first two events have a duration column in (microseconds) which you could filter on for your thresholds. They also have a resource_description action which will give you some detail on the resources involved. The lock_escalation ...


8

As you are specifically interested in locking rather than general waits the locks_lock_waits extended event sounds more suitable. With a filter on increment >= 200 CREATE EVENT SESSION [locks_lock_waits] ON SERVER ADD EVENT sqlserver.locks_lock_waits( ACTION(sqlserver.sql_text) WHERE ( [sqlserver].[is_system] = 0 ...


4

While waiting to hear back on a few questions that I posted in a comment on the Question, I will at least reiterate one of my questions: "What about your current stats leads you to suspect that SQLCLR usage is in any way related to performance issues?" From what I see of your output, SQLCLR is taking up very little memory. It has 110 MB of physical ram for ...


4

You could shard by location. Partition the globe into a grid and have each square in that grid on one server. Since you mentioned cloud, that would be well suited to cloud. Of course your will need to manually merge the results from multiple servers. That way you can use any database solution your like. It does not need to be scalable on its own. The ...


3

It sounds like there are two classes of query - one to understand which locations lie within the current view window and a second to deliver the desired statistic for those points. My suggestion is to use separate, specialised tools for each. I'm assuming all measurements relate to the same set of 75Bn points. These lat/longs, once established, are ...


0

Even 2 same indices on same column would be 2 different physical indices. You can check the index file name with : select relfilenode, relname from pg_class where relname = 'index_name'; Correlate the physical files in : ~PGDATA/base/db_oid/relfilenode


8

How up-do-date do your read queries need to be? You could partition the database by time if the map just needs to show the most recent measurement. This would reduce your query load for the map. For the history of a given point, you could hold a second store by x and y showing the history. This could be done with a nightly refresh/update as the the ...


2

How can the 2 indexes be physically the same? The values of id could be more example: 1,2,3,4,6,7,8,9 while the parent_id values could be: NULL,1,1,8,NULL,NULL,2,NULL So the answer to the question is: No, they would be two completely separate and different structures. It doesn't make any other sense. Even if the parent_id were all, one by one, ...


5

You are performing an index rebuild on Standard edition. As documented here you would need Enterprise or Developer edition to see parallelism for this.



Top 50 recent answers are included