Hot answers tagged

24

Should I always use (n)varchar(max) for text columns? No. For SQL Server, the max data types should only be specified when there is no alternative. One should instead choose the correct base type (varchar or nvarchar) and specify an explicit maximum length that is appropriate to the data to be stored. Physical storage is identical whether the column is ...


14

Is there any negative performance impact for using the same service account and agent account to run SQL Server and SQL Agent respectively for all SQL server instances running at a small company of say 35 servers? No. Service accounts do not affect performance in any way. Its all about security ! From the SQL Server 2012 security best practice whitepaper ...


7

This is going to read like a paranoid's answer, but there aren't only storage and performance considerations. The database itself doesn't control its clients, and clients can't be assumed to always securely insert user input - even if a database is designed to be used only with a .net application that uses Entity Framework to encapsulate transactions and ...


7

The real problem with using one Service Account for all of your servers is: How secure do you want to be? If someone is able to hack that one account, then all 35 servers are exposed in one swoop. I much prefer at least a Service Account per server. And having several Service Accounts for different uses is also recommended for security. See: Configure ...


4

There is no negative performance impact to using the same account on all your servers. If a change happens to that account, however, it will impact all your services using that account. You may want to consider different accounts for different environments (e.g. DEV, TEST, PROD).


3

That depends on your disk subsystem. A full backup is going to involve a metric crud-load of I/O (that is a technical term). That is going to put a huge load on your I/O subsystem. Depending on how I/O bound your system is, how much spare bandwidth your I/O subsystem has, how fast your I/O subsystem is, etc. the impact will range from "yeah, maybe it's a ...


3

Have you considered: Ensuring your database isn't auto-growing. The fact that it is slowing down after 30-40 million rows could be caused by auto-grow. For loading, try bcp in/out instead, and set database to bulk-logged recovery model. Just be aware of the pitfalls with this recovery model, in the case something bad happens to the db during this time. ...


3

Performance issues can stem from a wide variety of issues - the size of the table is only one factor. Things such as indexes and the design of the query are just as, if not more important than, how well something performs. For example, take a look at the advice on this Stack Exchange question: Slow Performance Inserting Few Rows Into Huge Table Once your ...


2

That is a new feature, part of the Galera cluster support. Galera replication is for InnoDB tables (MyISAM replication can be enabled but it is experimetal) and as it uses row-based replication, the tables should have primary keys to work right and fast. But you cannot just add them if the tables and applications were not designed to have them. You will ...


1

Yes, you are right. Memory is used by dormant databases I mentioned this in the following posts over the years Nov 13, 2015 : #1041 - Out of memory issue in mysql Apr 22, 2014 : Do Inactive MySQL Databases Consume Memory? Apr 21, 2014 : Adding new tables -- memory usage increases Lots of tables ? Lots of columns ? Of course, that is less memory for ...


1

InnoDB uses "optimistic" MVCC - when you do some changes, it applies them to the real tables and may even write them to disk. Commit only ensures that all the writes were finished and makes the changes visible and is otherwise almost instantaneous. That means that the rollback has to undo all these changes, possibly rewriting the pages on disk again and it ...


1

If you mark the functions as WITH SCHEMABINDING, then this may help - particularly if the function doesn't access any tables.


1

I confirm that a full or incremental backup during business hours can make your database inaccessible to end users and web services. Factors that can make things worse: allocating too many channels to RMAN using an out of support version like Oracle 10 having a database that is CPU or memory or network IO bound running Dataguard at the same time ...


1

(post_type, post_modified_gmt, post_status) is 'optimal' because it is (1) covering, and (2) ordered in perhaps the best way, and (3) the table is MyISAM, yet this index is much smaller. MyISAM is a bad choice for a table with a lot of bulky (TEXT) columns. InnoDB would probably run faster. MyISAM is prone to fragmentation. If there has been a lot of ...


1

There are many open ends in your question, but partitioning by customer could to be the way to go - especially if: you expect many customers, each of them could have tons of data ("tons" means much more than RAM cache size), most of their datasets will be mutually exclusive (each customer sees different subset of data). RULEs or triggers are a ...


1

I can think of a perfect case for it, and we have tested thoroughly and run it in production...I call it the "fast lane" clustering strategy: If you do read-write splitting with a proxy like MaxScale, or your application is capable, you can send some of the reads for those seldom invalidated tables only to slaves that have the query cache turned on, and the ...



Only top voted, non community-wiki answers of a minimum length are eligible