CODEWITHSUNDEEP
sql-server
user2852575
user2852575

Reputation:

How Allocate more memory to mssql

I just want to know that how I can allocate more memory to MSSQL management studio, so that it takes less time to run long queries. because I have to import and manipulate large amount of data form access files and other, where as I have 32 Gb ram for this purpose. so tell me solutions. thanks

Upvotes: 2

Views: 4364

Answers (1)

SchmitzIT
SchmitzIT

Reputation: 9552

SQL Server Management Studio (SSMS) is just a client tool.

If queries are running too long, the issue will be at the actual SQL Server. You can assign extra memory there by using SSMS to connect to the server, right-click it, and then select Properties.

On the Memory page, you can configure how much maximum RAM you want to assign to SQL Server. In your situation, I'd not assign more than 30 GB (leave some for the OS. In case your SQL Server is not the only application running on the machine, assign even less).

If you're dealing with large amounts of data that need to be imported, RAM might not be the issue, though. Most likely the bottleneck will be the disk system. Use Performance Monitor to try and get a clue as to where the real bottleneck is.

Some ways to enhance performance for the disk system is to ensure your drives are configured properly (General rule of thumb is to place transaction logs on RAID 1 partitions, and data on RAID 10 (or 5)). If you can afford it, place indexes on separate RAID partitions. Also make sure the database and drives are regularly defragged.

Upvotes: 4

Related Questions