Tag Info

Hot answers tagged

14

Standard Edition is limited to a 64 GB buffer pool. To use all the available memory of the server, you'll need to upgrade to either Enterprise or Datacenter Edition, or scale out to multiple instances of Standard Edition.


12

In the SQL Server error log, it should tell you why. I found this on a customer system (really just pasting the message for Google juice): SQL Server detected 4 sockets with 8 cores per socket and 16 logical processors per socket, 64 total logical processors; using 40 logical processors based on SQL Server licensing. This is an informational message; no ...


11

... was hoping I could get ... a rough rough estimate of what we should be running. Without more information about your queries and data sizes, it's really difficult to give you any kind of estimate, let alone an accurate estimate. Database: sql server 2008 r2 enterprise database Windows: Windows 2008 r2 Enterprise 64 bit, pretty sure running ...


10

Variants of this question come up semi-regularly: SQL Server build Help me choose a RAID level combination for a SQL Server 2008 instance How should I configure these disks on a SQL Server for a BI configuration? There are also occasional bun fights about the data/log separation "best practice". Placing Transaction Log on Separate Volume [solid ...


9

Typically the features that you don't hear about from marketing are the ones that don't bring in all the money (e.g. pushed as "enterprise feaures" in order to sell Enterprise Edition). I answered a similar question here that provided a list of my favorite new features in 2012, that also aren't limited to Enterprise Edition: What are Objective Business ...


8

With spinning-platter disks you want to have the logs and data on separate drives as random access data disrupts the sequential log write operations, making the logs a performance bottleneck. SSDs do not have this issue as they lack the performance constraints imposed by the mechanical action of conventional hard disks. If you're getting SSDs for a DB ...


8

This is account 'bean counter' insanity. The $1100-$2500 you would spend on RAM could possible pay for itself back within a week! They are getting time outs for 20 employees and 5 of them are doing 'resource intensive' work. I'd imagine their time isn't cheap, and some of those reports are the ones the boss not signing off on the paycheck would love. ...


7

Correct. In theory, if you have 100s of DBs you need 100s of drives, one for each log. In practice though one does not care for such case, cause when you have 100s of DBs you obviously don't expect top-notch TPC performance for each DB. You will likely have some DBs with high throughput and stringent SLAs and you could have them each on separate spindles, ...


7

SQL Server 2008 R2 installed on a virtual server IMO, the SQL Server VM should live with the rest of the VMs. If this device goes kaput (the Cybernetics website says nothing about the number of controllers in this model range, so presumably there's only one), you don't want all your eggs in one basket. Hopefully there are other, better protected devices ...


6

Even though you have memory to spare at the moment (70GB database vs 128GB server) I would still configure the maximum server memory as per latest guidance. ...reserve 1 GB of RAM for the OS, 1 GB for each 4 GB of RAM installed from 4–16 GB, and then 1 GB for every 8 GB RAM installed above 16 GB RAM. That equates to 110GB on a 128GB server. ...


6

If there is room for more drives, order more drives. If there isn't room for more drives, order more drives... and an external enclosure. I'm assuming this data has at least some value to the business and from your comment we can also infer that you're experiencing performance issues already. So, zero redundancy and single spindle performance isn't going to ...


5

"Isn't it possible to scale database servers through virtualization, like forever?" Yes, you can make more and more database servers and you can also make a larger and larger database server (but the scale up limit can be reached very quickly), but in the scale out scenario are the servers you mention supposed to cooperate in any way or are they ...


5

It all depends on your workload, but with only 6 drives it does limit your options. If your workload is not heavily dependent on tempdb for things such as sorts, hash tables, and snapshot isolation, then you might be better off using the 6 SAS drives together in RAID 10. However, if you know or have the metrics to prove that tempdb is heavily utilized, ...


5

Speaking from experience which is humble but I think worth sharing, the major bottleneck with SQL databases (Sybase and SQL server here) is storage. But I think it's only fair that someone first benchmarks their setup before making any wrong assumptions. In my case, CPU usage has never risen high enough to justify upgrading the CPU any time soon. Instead, I ...


5

This is essentially a hardware capacity planning question. The problem with them is that if you give enough details that we can tell you what hardware you need, it'll become "too localized" to your particular set of circumstances. That said, we've got a number of pseudo-answers already written to help you: Can you help me with my capacity planning? How do ...


5

Previous to SQL 2012, all SQL Server files had to be on a shared cluster resource. It's not so much an issue of the recognition of a local path, but just that SQL Server won't allow you to use local storage in a clustered installation. This is problematic for SSDs because it is usually to expensive to attach SSDs as anything other than direct attached ...


4

Quoting from Brent Ozar's article SQL Server Virtualization Q&A: Question: “Regarding Virtualization and SAN… is there a recommendation for setting block size on SQL Server 2008?” There’s a few related settings here including NTFS allocation unit size, RAID stripe size, and partition offset. Check with your SAN vendor’s documentation to ...


4

+1 for the answers from @CadeRoux and @ChrisS, they make valid points. Your comments to those answers highlight that this is essentially a proof-of-concept venture at this stage and you want to minimise your capital investment. If that's the case, forget spending $1000s on hardware and licenses, rent. You don't appear to need to store a vast quantity of ...


4

From the comments chain, it looks like you're interpreting ASYNC_NETWORK_IO waits to mean that the problem is related to the network. It (typically) isn't. As @MartinSmith hinted at (twice) the most likely explanation for that is SSMS or the application you're using not consuming the results as fast as SQL Server is serving them. Follow either of the ...


4

If you were using Enterprise Edition, I would recommend partitioning. Separate files on separate logical drives is unlikely to provide much help, since the data is all going through the same controller and writing to the same underlying disk (and without partitioning, you don't have much control over proportional fill, meaning many queries will likely still ...


4

Look in the ERRORLOG from when SQL Server first fired up. It'll tell you how many CPU's it's using, and probably why. If you can post the ERRORLOG file somewhere that'll be helpful in seeing what's going on.


4

If you have a single RAID10 volume then as far as SQL Server is concerned you have one volume and you can't control how things are stored, splitting things into extra files unnecessarily will likely have detrimental effects as it would on a single disk. If you wish to try gain performance benefits from segregating data between spindles then you need to ...


3

Shrinkfile on a datafile is a single-threaded operation, reusing a small memory buffer. So the Ninja hardware hasn't got an edge with the extra memory and the 80 cores. Your local PC however has the benefit of local I/O latency (local disk, i.e. not having to make multiple trips to the SAN).


3

Yes you'll want 64k blocks on the array and the disk as SQL Server will be doing the bulk of it's operations in 64k chunks. While there will be some reads which are smaller than this and some that are larger (such as read ahead) the bulk of the operations will be 64k in size. Assuming that the server is a Windows 2008 server (or newer) and that the LUN is ...


3

It's hard to know / to figure out: Even some simple reads, could be IO hungry. And the amount of users doesn't tell us how many request ( read / write). Without more data, it hard to tell, but that seem to be a good server. I believe that it'll be good enough, but that's just a opinion.


3

Based on everything you have said in the question and the comment, I don't think you need to worry about hardware yet unless you are trying to just get a ballpark price estimate for feasibility. Since you are a software person, I would build the prototype on commodity hardware like your ordinary laptop, analyze and understand the problem and then spend ...


3

If you can afford it, put pg_xlog on a separate RAID 1 pair of drives on its own controller with battery-backed RAM configured for write-back. This is true even if you need to use spinning rust for pg_xlog while everything else is on SSD. If you use SSD, make sure it has a super-capacitor or other means to persist all cached data on power failure. In ...


2

Ideally, you'd have the same configuration in development as you do in production. That way, you can have a higher probability that if it's slow in dev, it will be slow in prod, and if it's fast in dev, it will be fast in prod. I'm guessing that this would be a pretty significant drop from what you have in production. But given that few places have the ...


2

Yes it is always safer. Thought the chance of actually getting corruption is low. When the filesystem gets corrupted it is very likely that repairing it will be successful. ACID compliant databases like InnoDB also do fsyncs/barriers to make sure committed changes are permanently stored on disk. Don't forget that in a production environment you should be ...



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