Top 5 OLTP performance improvements you would like to see in the next version of SQL Server
Hello Everyone,
We are currently looking at top performance issues for various workloads and how we can improve those in next version of SQL Server. We are collecting feedback from various customer sources. I would like to extend an invitation to readers here for feedback on top 5 OLTP performance improvements you would like to see in SQL Server.
Please bear the following in mind:
1. Briefly describe the OLTP workload or scenario or application. More details you can provide for us the better
2. Prioritize the list of performance improvements in order of importance or impact to your workload. You can also just highlight the top 5 performance problems with running your OLTP workload in SQL Server
3. Feel free to suggest any feature(s) that will help in improving the performance of the particular OLTP workload or scenario or application
I will consolidate the feedback and post summary version of it as a link in this post.
Thanks
Umachandar
Comments
Anonymous
September 20, 2006
Partitioning and load balancing (I've writtten to Lubor Kollar about it)Anonymous
September 21, 2006
The comment has been removedAnonymous
September 21, 2006
To compete in the Grid Computing space w/Oracle ( RAC )Anonymous
September 22, 2006
Thanks for your comments Stefan. I will ping Lubor and get your feedback.
--
UmachandarAnonymous
September 22, 2006
Better support for those of us, trying to use static filtering:
WHERE (col1 = @col1 OR @col1 IS NULL)
AND (col2 LIKE @col2 OR @col2 IS NULL)
AND (col3 > @col3 OR @col3 IS NULL)
...
Another scenario is when the filters are stored in a filter table (this is because the filters actually arrive as XML to SS2005):
WHERE (col1 = ISNULL((SELECT Filter FROM Filters WHERE colname = 'col1'),col1)
AND ....Anonymous
September 24, 2006
If you use SQL Server and have a great suggestion for performance improvements to the engine then post...Anonymous
September 24, 2006
If you use SQL Server and have a great suggestion for performance improvements to the engine then post...Anonymous
September 25, 2006
Would it be possible to skip (unnecessary) checking of a DRI constraint in an UPDATE statement if the FK column's value has not changed?Anonymous
September 26, 2006
This may seem a very odd request at first so I will give you some background to start with.
We have a build server that first builds all our C# code then runs all our tests, so fare so good. Part of the build process is to run all the sql script that create database tables etc, then run a lot of unit tests that talk to the database. Each of our tests runs within a database transaction, we then abort the transaction after the test, so as not to change the state of the database.
Therefore I would like a type of transaction that is VERY fast to run and abort, e.g. can I tell sql server at the start that I will never commit the transaction so that it can run faster? Likewise as I don’t care about anything in the test database, can I tell SQL server to run faster and not care about being able to recover after a reboot etc?
(After starting the transaction, we delete all data form all tables, before putting in the data that the given test needs)
Ian Ringrose
www.ringrose.name
email address on websiteAnonymous
September 26, 2006
please fix the SQL 2000 problem 469591
("When using sp_cursoropen with an Order By Clause we Generate an Inefficient plan ")
if this is breaking existing apps, please consider a new cursor type / option or API functionAnonymous
October 02, 2006
The comment has been removedAnonymous
October 05, 2006
I'd like to speed up developing and debugging - so slightly off topic, sorry!I would like to see some sort of "lint" warnings, e.g.If an implicit cast is used report it as a warning - e.g. by setting an optional higher warning level (like a C compiler has)If NO order by is specified return the data in random order - so that missing ORDER BY statements get picked up in Testing (or provide a Warning as above)Would separate DATE & TIME data types be considered a performance improvement? Fewer bytes in the record of course. Less need for triming the time off the date and so on.I suppose the much heralded UpSert statement is out of the question?Anonymous
October 09, 2006
SQL Server 2005 unique indexes should allow multiple null values, restricting just not null values. create table a ( a int null) create unique index ix_a on a(a) insert into a values (0) insert into a values (null) insert into a values (null)Anonymous
October 12, 2006
- STABILITY AND TESTING
- describe keyword.. like in Oracle.
- and of course.. the limit keyword-- like in mySql.
- ability to consume Olap data in relational world easier
- simple data entry forms and reports-- like Access Data Projects - but tested and bugfree.. fast stable, dependable
Anonymous
October 12, 2006
a) better BUGFREE gui design tools b) optimization tools for sql statements c) better wizards for building tables.. think 'table wizard' from mdb d) auto-configure diskspace e) easier, zero-configuration email sending.. allow it to go through hotmail or livemail-- or something external so that your local exchange Admin CANT lock you out.Anonymous
October 12, 2006
fix sql authentication; it's still a security concern make it secure by default 2/3rds of the companies i've been at over the past decard use sql authentication for SOMETHING. make it easier to conceptualize AD security tokens.. make something easily visible in a GUI that would allow you to say 'I am User TOKEN#1221445453.. let me KEEP this token in a table; so that I can then use it to query this table a week later.Anonymous
October 12, 2006
The comment has been removedAnonymous
October 12, 2006
i wish that every database in the world had a table called C that listed characters in ANSI and Unicode format... so it would be easy to display the letters A, B, C, D, F without writing our own table... So that it would be easy to filter our phone lists for people that start with the letter S... so that we could filter a list of cities for cities that start with the letter M. I wish that every database had a couple of date (dimension) tables.. and that they were called the same thing in every database in the world.. for translating between a date and an integer and a month and a year; etc.. and for displaying a list of every tuesday between now and christmas. I wish that every database in the world had a table called N where I could easily say 'between 1 and 37'.. so literally.. just a table called N that lists numbers from say; one to 32,000.... and if I ever need to display a list of numbers in my query; then it's easy to say 'between 1 and 1700' I would just write a simple sql statement and I would be done already. seriously though. I wish that I could 'subscribe' to an RSS table that would update once a month or something.. for ZipCode updates.. rather than having a dts package run once a month-- make it built into SQL Server. right-click LINK to RSS source and set a schedule. EVERYBODY DOES THESE THINGS DIFFERENTLY EVERYBODY DOES THESE THINGS DIFFERENTLY having a standardized set of tools to do these sorts of things should make things easier for developers throughout the world.Anonymous
October 12, 2006
select * {like '%id'} from myTable this would return all of the columns from my table that ended with the letter 'id' select * {like '%id' abc} from myTable this would return all of the columns from my table that ended with the letter 'id' and it would put the columns in alphabetical orderAnonymous
October 12, 2006
Improve user defined aggregates by defining scan order. I stored procedures and functions using cursors that can't be changed to aggregates because there is no scan order guarantee in aggregates.Anonymous
October 13, 2006
One is to have something similar to Oracle RAC, where multiple servers can work on the same data files. Two is leapfrog Oracle, by developing "two tier" replication as described by Jim Gray. http://research.microsoft.com/~gray/replicas.docAnonymous
October 15, 2006
i need i dataflow destination in SSIS that will send uncessary data to other space i mean a destination that eats the data and its goneAnonymous
October 15, 2006
i need i dataflow destination in SSIS that will send uncessary data to other space i mean a destination that eats the data and its goneAnonymous
October 16, 2006
General note. Please do not post general feature requests. We are looking specifically for performance issues only. And if you think that a specific feature will help improve performance then please comment briefly about that scenario. Thanks UmachandarAnonymous
November 14, 2006
Umachandar, Sometimes, one needs to bulk insert, update or delete a huge amount of data and he doesn't necessarly waist time logging all these operations It would be noce to have an option to do it like the one we have with SELECT INTO, BULK INSERT or BCP tool MedAnonymous
November 14, 2006
Umachandar, Something that mae my life easier is a command that INSERT or UPDAYE automatically rows in a table MedAnonymous
December 05, 2006
The comment has been removedAnonymous
January 22, 2007
Stop flushing the cache server-wide for events that are specific to a database. For example, detaching or dropping a production database (and other non-exciting events) currently causes this drop cache event found in trace SP:CacheRemove with a textdata "2 - Proc Cache Flush". To drop a database during the middle of the day should not be a critical performance impact as it currently is. Thanks, Robert TowneAnonymous
February 14, 2007
An unqualified DELETE (a delete without a WHERE clause) should be processed the same as a TRUNCATE TABLE when the table has no foreign keys referring to it and no delete triggers. Why bother going through the row-by-row delete, including all the logging overhead when it could just deallocate the pages like TRUNCATE does.Anonymous
October 03, 2007
I would like to be able to delete large chunks of data without having it logged from time to time, there by speeding up the process. Presently SQL Server will log every row that is deleted. I know TRUNCATE is an option, and that this wont log individual rows, but sometimes I dont want to delete everything in the table. Perhaps a hint of some kind ie.. DELETE FROM tblData ( with nolog ) WHERE datatype = 'bad data'Anonymous
June 17, 2008
I would love to see a nolog hint for delete, as Kevin Dockerty suggested.